Looping all columns in SSIS Script Component

Quite new to SSIS but working on an old SSIS 2005. Was attempting to make a foreach to get all column values from a row.

Dim column As IDTSInputColumn90
Dim pInfo As PropertyInfo
Dim rowType As Type = Row.GetType()

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
            pInfo = rowType.GetProperty(column.Name)
            varColValue = pInfo.GetValue(Row, Nothing).ToString()

That didn’t actually works. Some column able to get the value, some don’t. Then I found out, the column.Name not the same with the property exposed by rowType.GetProperty.

column.Name Property exposed by rowType.GetProperty
SAP Company Code SAPCompanyCode
Cost Object (WBS/CC) CostObjectWBSCC

So, have to do some changes and the following codes works:

For Each pInfo In rowType.GetProperties()
            If pInfo.PropertyType Is GetType(String) Then
                varColValue = pInfo.GetValue(Row, Nothing).ToString()
            End If

This pretty useful if you wanna do something on all the columns with the same function. I have seen examples of changing all columns value to uppercase. (Derived Column component have to adjust all columns one by one)


1 thought on “Looping all columns in SSIS Script Component”

Leave a Reply

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