The Script Transformation part 2 – as a Source

Fig 1: The Script Transformation
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:

b
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:

b
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:

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.

4 thoughts on “The Script Transformation part 2 – as a Source

  1. Hello,

    This one is pretty clear, but I’ve a question regarding creation of dynamic OutputColumns with Output0Buffer.

    Could you please tell me how can I create dynamic columns (for example:RowID and RowName) using Output0Buffer in Script Component with C# or vb.net

    [ like
    Output0Buffer new_column= Output0Buffer.New();
    new_column.Name=”RowName_YYYYMM” // YYYYMM depends on date which should be dynamic
    ]

    Bcoz my columns names are to be varied often which i get from my client (column name with YYYYMM as suffix).

    i cannot change the column names of output buffer every month..

    Thanks in advance
    Santosh Kumar

  2. SSIS doesn’t natively allow for this sort of behaviour – the underlying principle of how it operates is fixed metadata – so you cannot change column names dynamically at run time.

    However I believe this is possible using the Data Flow Plus Task available from Cozyroc.

  3. Hello, I am using a source type script component. The CreateNewRows() sub will not fire. I was wondering if you had some insight?

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    ‘Need to check if there is an internet connection
    Imports System.Net
    Imports System.Web
    Imports System.Data.SqlClient
    Imports Microsoft.IE
    Public Class ScriptMain
    Inherits UserComponent
    Private Shared SFDCDataSet As System.Data.DataSet
    Public Shared SFDCAccountTable As System.Data.DataTable
    Public Overrides Sub PreExecute()
    Dim TheArguments(2) As String
    ‘Validate that there is an internet connection before processing anything
    Dim ValidInternetConnection As Boolean = HTTPAvailable()
    If ValidInternetConnection Then
    TheArguments(0) = “XXXXXX”
    TheArguments(1) = “XXXXXX”
    SFDCDataSet = TheInterface.MainSF(TheArguments)
    End If
    End Sub

    Public Overrides Sub CreateNewOutputRows()
    Dim dataRow As System.Data.DataRow
    If SFDCDataSet.Tables(0).Rows.Count > 0 Then
    SFDCAccountTable = SFDCDataSet.Tables(0)
    For Each dataRow In SFDCAccountTable.Rows
    Me.SFDCAccountTableBuffer.AddRow()
    Me.SFDCAccountTableBuffer.Market = dataRow.Item(“Market”).ToString()
    Me.SFDCAccountTableBuffer.AccountPhone = dataRow.Item(“AccountPhone”).ToString() Me.SFDCAccountTableBuffer.SalesRep = dataRow.Item(“SalesRep”).ToString()
    Me.SFDCAccountTableBuffer.Description = dataRow.Item(“Description”).ToString() Me.SFDCAccountTableBuffer.ContactFlag = dataRow.Item(“ContactFlag”).ToString()
    Next
    Me.SFDCAccountTableBuffer.SetEndOfRowset()
    End If
    End Sub
    End Class

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>