SSIS For Each Column in a Data flow

Previously I wrote a blog on how to do a for each loop to look through each col in an SSIS data flow here. Well things have changed since I wrote that blog, in fact I believe that old  code only works in SSIS 2005. So I thought I would be good to update to SSIS 2008 and show you new and better way to loop through all of the columns in a script tasks.

You could do the same work with a derived column. The problem comes when you have hundreds of columns and you need to do the same work on each column. The for each column saves a ton of time for development and make maintaining the code easier. It does hurt performance though. Derived columns perform much faster than script task.

The below code I found on the MSDN forums here. This code will replace all double quotes in every column with empty string. You can replace that one line of code with any work you need to do on multiple columns.

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
       Dim colstr As String = “”
       For counter = 0 To inputBuffer.ColumnCount – 1
           colstr = (inputBuffer.Item(counter).ToString())

           inputBuffer.Item(counter) = Replace(colstr, Chr(34), “”)

       Next
   End Sub

I tested  this code in SSIS 2008 r2. Let me know if it works in your version.

Advertisements
This entry was posted in SSIS, Syndication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s