如何在 SSIS 中展平结果集？

Question

我需要“展平”SSIS 包中的结果集 - 即获取由多行组成的结果集并生成包含连接值的单个字符串。

结果集通过存储过程返回给我，由于多种原因我无法更改该存储过程。我不想将逻辑复制到另一个存储过程中，因此限制是我需要从存储过程的结果开始并从那里开始工作。

我当前正在使用带有 ADO.NET 连接的“执行 SQL 任务”来运行存储过程，并将结果返回到对象类型的 SSIS 变量中。

我当前的想法（来自谷歌搜索等）是使用“执行脚本任务”，使用类似以下内容：

    Dim flattened As New StringBuilder()
    Dim adapter As New OleDbDataAdapter
    Dim dt As New DataTable
    Dim row As DataRow

    adapter.Fill(dt, Dts.Variables("DataSet").Value)

    For Each row In dt.Rows
        flattened.Append(row("Column").ToString)
        flattened.Append(DELIMETER)
    Next

    Return flattened.ToString

上面的示例当前返回错误：“对象不是 ADODB.RecordSet 或 ADODB.Record。”我已经尝试了上述方法的几种不同变体来尝试解决此问题，但已经达到了我在环境中调试时遇到的挫败感。

任何想法将不胜感激！ 谢谢

Answer 1

这是一个在sql中动态展平表的sql示例：

GO
CREATE TABLE CarDealer (
  DealershipID int not null,
  CarColor char(16),
  Model char(16),
  UnitsSold int
);

insert into CarDealer
    select 1, 'Yellow', 'Toyota', 5

insert into CarDealer
    select 1, 'Yellow',  'Ford', 8  

insert into CarDealer
    select 1, 'Red',  'Toyota', 3

insert into CarDealer
    select 2, 'Blue',  'Ford', 7
go

select * from CarDealer

DECLARE @PivHeaders VARCHAR(MAX)

SELECT @PivHeaders =   COALESCE(rtrim(@PivHeaders) + ',[' + rtrim(Val)+ ']',  '[' + rtrim(Val) + ']')
FROM 
(
    select distinct rtrim([VehiclesSold]) + ltrim(Color) as Val
    from
    (
        select * from CarDealer
    ) s1
    UNPIVOT
    (
        VehiclesSold For Color in
        ([CarColor], [Model])
    ) unp        
) s1

--print @PivHeaders

DECLARE @PivotSQL NVARCHAR(MAX)
SET @PivotSQL = N'select DealershipID, p.*  from 
(
    select UnitsSold, rtrim([VehiclesSold]) + ltrim(Color) as Val, DealershipID
    from
    (
        select * from CarDealer
    ) s1
    UNPIVOT
    (
        VehiclesSold For Color in
        ([CarColor], [Model])
    ) unp        
) s1
PIVOT
(   
    Sum (UnitsSold)
    FOR Val
    In 
    ('
        + @PivHeaders + '
    )
) p'

EXECUTE(@PivotSQL)

Answer 2

不确定这是否是最好的方法，但我发现有效的方法是：

Dim dt As DataTable = CType(Dts.Variables("DataSet").Value, DataSet).Tables(0)

然后在循环中循环 dt 。

如果有人知道更好的方法，请仍然回答，我会相信你的答案。如果没有，那么会将这个答案留给未来遇到同样问题的人。

谢谢