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, 2011 at 09:01 AM in Default

avatar image

Raj More
1.8k 82 87 90

(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, 2011 at 05:45 PM

avatar image

Daniel Ross
2.9k 11 15 18

@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, 2011 at 08:13 PM Oleg

Here is the link to telerik code converter

Jan 18, 2011 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.

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:

x1147
x741
x5

asked: Jan 18, 2011 at 09:01 AM

Seen: 1935 times

Last Updated: Jan 18, 2011 at 11:13 AM

Copyright 2016 Redgate Software. Privacy Policy