asked
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?
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
Here is the link to [telerik code converter][1] [1]:
http://converter.telerik.com/
@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);
}
}
