x

SSIS transformation

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?
more ▼

asked Jan 18 '11 at 09:01 AM in Default

Raj More gravatar image

Raj More
1.7k 77 82 84

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jan 18 '11 at 05:45 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 13

@Daniel This is a very good answer! By the way, if you ever need to convert existing code to/from VB/C#, the best converter I have ever seen is web-based telerik converter. For example, I just copied your code there and it came up with this:

public class ScriptMain : UserComponent {  string TagID = "-1";  string TagList = "";
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    //
    // Add your code here
    //

    TagID = Row.Column0;
    TagList = Row.Column1 + ",";

    while (Row.NextRow) 
    {
        if (Row.Column0 != TagID) 
        {
            Output1Buffer.AddRow();
            Output1Buffer.Set = TagID;
            Output1Buffer.ValueList = 
                TagList.Substring(0, TagList.Length - 1);
            TagID = Row.Column0;
            TagList = Row.Column1 + ",";
        } 
        else 
        {
            TagList += Row.Column1 + ",";
        }
    }
        Output1Buffer.AddRow();
        Output1Buffer.Set = TagID;
        Output1Buffer.ValueList = 
            TagList.Substring(0, TagList.Length - 1);
}
}
Jan 18 '11 at 08:13 PM Oleg

Here is the link to [telerik code converter][1]

[1]: http://converter.telerik.com/
Jan 18 '11 at 08:14 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x562
x4

asked: Jan 18 '11 at 09:01 AM

Seen: 1503 times

Last Updated: Jan 18 '11 at 11:13 AM