Rick Caminiti dotCOM

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