Generate ROWNUM pseudo-column in SSIS
I posted earlier on how to generate a rownumber in SQL 2000, but that would not work as a source query in SSIS for some reason. Without having to create a stored procedure or temp tables based on other temp tables, I wanted a simple way to generate this rownum in SSIS. I was considering something with variable expressions, but then I found a way to do it with a Script Component.
Drag a Script Component Task into your DataFlow and connect it between your source and destination tasks. Open it up and click “Inputs and Outputs”. Double Click “Output 0″ and highlight “Output Columns” and click Add Column. For this code to work, name that new column “rownum“.
Then open the script tab and click “Design Script” and paste this code over what is already there:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer = 0
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.rownum= counter
counter = counter + 1
End Sub
End Class
Recent Buzz