SSIS transformation

Question

I have the following data coming in to SSIS from an XML input file

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

I read the values from the XML source and then using a script component I am trying to using the following logic

public class ScriptMain : UserComponent
{
    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)
            {
                IsFirstRow = false;
            }
            else
            {
                Output1Buffer.AddRow();
                Output1Buffer.TagId = TagId;
                Output1Buffer.TagList = TagList;
            }
            TagId = Row.TAGSId.ToString();
            TagList = Row.TAG.ToString();
        }        
    }
}

However, this part of the script never executes. When I send the values read from the XML file straight to a Flat File destination, it does work, but for some reason this part isn't working.

Is there some setting that I am missing?

Answer 1

It looks like you are close, but you may be complicating it a bit too much. Using Row.NextRow in the script will help you. Sorry about the VB, I have 2005 and don't have C#.

The main problem with your code seems to be that you don't know which row is the last row in the buffer, so you are always going to miss the last "SET".

(Warning, there have been changes in the way 2008 behaves with row.nextRow and it might cause an error. Just make sure that you are getting all of the rows, and not getting duplicate rows. http://blogs.msdn.com/b/michen/archive/2008/10/19/does-buffer-nextrow-skips-the-first-row-in-a-buffer.aspx) This works perfectly in 2005 using VB

Public Class ScriptMain
Inherits UserComponent

Dim TagID As String = "-1"
Dim TagList As String = ""


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    '
    ' Add your code here
    '

    TagID = Row.Column0
    TagList = Row.Column1 & ","

    While Row.NextRow

        If Row.Column0 <> TagID Then
            Output1Buffer.AddRow()
            Output1Buffer.Set = TagID
            Output1Buffer.ValueList = TagList.Substring(0, TagList.Length - 1)
            TagID = Row.Column0
            TagList = Row.Column1 & ","
        Else
            TagList += Row.Column1 & ","
        End If


    End While
    Output1Buffer.AddRow()
    Output1Buffer.Set = TagID
    Output1Buffer.ValueList = TagList.Substring(0, TagList.Length - 1)

End Sub

End Class

SSIS transformation

Follow this question

Related Questions