SSIS Script Component Store all rows

Question

I have an SSIS script component which takes in rows from input.

One of the rows on the input is a boolean which identifies if the row is of type A or B.

How do I get the script to load all of the input rows into the correct collection of rows.

Also any guides on how to use the row collections and then output them would be great too.

Answer 1

Since the script component is using VB, it would be something like

If Column(type) = True Then
  CollectionA.Add(row)
Else
  CollectionB.Add(row)
End If

However, you may want to look into using a DataTable, as it more closely represents what you are trying to store.

There is an event that you can tie to that executes when all the rows have gone through the component. When that event executes you can move the data from the DataTables into some variables that exist in the dataflow. If you type these variables as Table values, I believe that you can ten work with those variables in the dataflow.

Your best bet may be to put this entire operation into a seperate DataFlow component within your control flow, as it sounds as though you may be changing the number of rows coming out, and your best bet may be to use multiple DataFlow components.

Answer 2

You may be able to use something like what Jamie Thomson did in his blog entry here:

http://consultingblogs.emc.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

He uses the script component and splits the output into several output streams depending upon conditions. He explains very concisely in his post how to configure the component and how to send the proper rows to the proper output stream. Hope this is what you were looking for.