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()
        Next

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.
Example:

column.Name Property exposed by rowType.GetProperty
USR_ACCESS_REQUEST_ID USRACCESSREQUESTID
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
        Next

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)

Reference:
http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/c1278230-b3e4-49bd-9738-334ab5ecf2d4

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

Leave a Reply

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