SSIS transformation (almost like a pivot)

Question

I have the following data coming in to SSIS

Set   Value
---   -------
1     One
1     Two
1     Three
2     Four
2     Five
2     Six

I want to transform it to read

Set   ValueList
---   -------
1     One, Two, Three
2     Four, Five, Six

How do I do this in SSIS?

Answer 1

There is a pivot task in the data flow transformations. You could try it, but I'll warn you that we have been less than hapy with it's implementation.

Alternatively, you could use the dataflow to put the data into a staging table, and pivot using SQL or do the pivot in the SQL you use to create the incoming data source. If you want to do it in SQl code, this might help:

select 1 as Item 
into #test 
union select 2 
union select 3 
union select 4 
union select 5 

select STUFF((SELECT  ', ' + cast(Item as nvarchar) 
        FROM #test 
        FOR XML PATH('')), 1, 1, '') 

Answer 2

I used the Script Component to do the string concatenation across rows

string TagId = "-1";
string TagList = "";
bool IsFirstRow = true;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.TAGSId.ToString() == TagId)
    {
        TagList += Row.TAG + ",";
    }
    else
    {
        if (IsFirstRow)
        {
            Output0Buffer.AddRow();
            IsFirstRow = false;
        }

        TagId = Row.TAGSId.ToString();
        TagList = Row.TAG.ToString() + ",";
    }

    Output0Buffer.TagId = int.Parse(TagId);
    Output0Buffer.TagList = TagList;
    Output0Buffer.TagLength = TagList.Length;

    //variable used in subsequent queries
    this.Variables.TagList = TagList;
}