Address Columns Generically In a Script Component

When writing script components in SSIS, it’s often useful to be able to process the columns without knowing exactly what they are. For example, you may want to loop through all the columns to check for a conditional, like NULL values in the columns. Or you may want to take a set of columns and concatenate them into a single string for output, or output each one as an XML element. In any of these cases, you don’t necessarily care about the individual column details.

One option is to use Reflection. This is fairly easy to set up, but is not the fastest performing option. To use Reflection, add this to the top of the Script class:

Imports System.Reflection

In the ProcessInputRow, you can use the following code:

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim column As IDTSInputColumn90

        Dim rowType As Type = Row.GetType()

        Dim columnValue As PropertyInfo

        For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection

            columnValue = rowType.GetProperty(column.Name)

            If columnValue.GetValue(Row, Nothing).ToString() = “January” Then

                columnValue.SetValue(Row, String.Empty, Nothing)

            End If

        Next

    End Sub

This example is checking the value of each column to see if it is “January”, and if it is, writing an empty string back to the column. Be aware that this option is not the best performing option. It’s OK for small data sets, but I would not use it for large ones. You can get better performance from the Reflection option by caching the PropertyInfo objects for each column in an instance variable, but if you are really interested in performance, keep reading.

This option is a bit more complex, but performs better. This is based off some code originally written by MSDN Forum member jaegd (original post here). It’s been simplified a good bit, but that introduces some limitations. As written, this isn’t very type safe, so be sure to add code to check the data type if you are not working with strings.

Public Class ScriptMain

    Inherits UserComponent

    Private inputBuffer As PipelineBuffer

    Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

        inputBuffer = Buffer

        MyBase.ProcessInput(InputID, Buffer)

    End Sub
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim counter As Integer = 0
        For counter = 0 To inputBuffer.ColumnCount – 1

            If inputBuffer.Item(counter).ToString() = “January” Then

                inputBuffer.Item(counter) = String.Empty

            End If

        Next

    End Sub

End Class

In the ProcessInput method, the Buffer object is cached, as we’ll need it to reference the columns by their index. In the ProcessInputRow method, we can use the Item property and the column index to read from or write to the column.

That’s two options for working with columns generically in the Script component. As mentioned above, use the second option for performance.

  GenericColumns.zip Compressed (zipped) Folder
48.8 KB GenericColumns.zip

As usual, I’ve posted a sample with both methods on my SkyDrive.