The Script Transformation part 2 ? as a Source

[James Beresford]

Fig 1: The Script Transformation


In this post I will be covering using the Script Transformation as a Source. The sample package can be found here for 2005 and guidelines on use are here.

How can you use the Script Component as a source?

One thing I skipped over in my previous post was that when you drag a Script Component on to the Data Flow surface, you are prompted as to whether you want to use it as a source, destination or transformation (the default). If you opt to use it as a source, it configures itself differently, so it has no inputs and a single output by default, to which you need to add columns, as below:

Fig 2: Adding Output Columns to the Script Transformation


To populate rows, in the code you add a row to the Output Buffer using the AddRow method. You then assign values to the columns that you specified – the columns will be properties of the Output Buffer which will appear on Intellisense. Each time you hit the AddRow method you close off the previous row for that buffer. Below is a simple example generating some rows of data using a simple code loop. Each time the loop starts a iteration, I call the AddRow method for the Output0Buffer (the default one created when you set up the component). Then I set the values for the RowId and RowName columns using them as properties of the Output0Buffer. In this case, the code speaks a thousand words:

Public Overrides Sub CreateNewOutputRows()

‘ Define a loop counter
Dim i As Int32

‘ Loop the counter between 1 and 10 with a For / next loop
For i = 1 To 10

‘ Call the AddRow method on the Output Buffer variable
Output0Buffer.AddRow()

‘ Set the RowId property of the Output Buffer to the Loop counter
Output0Buffer.RowId = i

‘ Use a Case statement to decide the RowName property value
Select Case i

Case 1, 2, 3
Output0Buffer.RowName = “Under 3″

Case 4, 5, 6, 7
Output0Buffer.RowName = “Between 4 and 7″

Case 8, 9, 10
Output0Buffer.RowName = “Over 8″

End Select

Next i ‘ Do the next iteration of the loop

End Sub

So it’s not really that complicated to build a simple source. For those looking for some more advanced capabilities i’ve also included a couple of extra examples in the sample package. In the first (2b) I output rows to multiple outputs, sending a different set of rows to each output. In the second I read data from a table in the AdventureWorks database within the script source and modify it a little before sending it out to the data flow. It’s important to note you need to use a ADO .NET (Datareader) connections to access a database through a script’s own connection manager collection. Adding an ADO.NET connection manager for use in the script is simple – as shown below:

Fig 3: Adding a Connection Manager to a Script Component


Note the Connection Manager is from the Connection Managers for the package, not internalised to the Script Component itself.

Where would you use the Script Component as a source?

I’ve used it a couple of times to generate rows (such as default values for dimension tables), and the other common use would be when you need to access a data source that cannot be reached by standard SSIS components, but can be coded up to return a set of rows out of VB.Net / C#, for example a web service, or a particularly awkwardly formatted text file.

MSDN Documentation for the Script Transformation can be found here for 2008 and here for 2005.

If you are still struggling, try these additional resources:

• SSIS – Using a Script Component as a Source (Phil Brammer at SSIS Talk)

If you need specific help or advice, or have suggestions on the post, please leave a comment and I will do my best to help you.



Get More from the original blog...