Feb 3, 2012

SSIS 2008 For Each Column in a Data flow

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), "")
   End Sub

No comments:

Post a Comment


Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.