FAQ Topics

All FAQ's

General DBA

General Developer

DBA Performance Tuning

Developer Performance Tuning

Clustering

Error Messages

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...

How to Generate an Auto Incremental Number in a SSIS Package?

Printer friendly

As many of you aware, you can have incremental integers in tables by setting the identity property for the column. In a T-SQL, you can do this by using the function named ROW_NUMBER(). In SQL Server Reporting Services you can acheive this by using RowNumber(Nothing). So how do you do this in SQL Server Integration Services (SSIS)?

Unlike the examples above there is no function to do this in SSIS. However you can aceive this using the Script component of SSIS by following the steps below.

1. Drag and drop the Script Component to the Data flow and select Script Component Type as Transformation. 2. Double click the Script Component.
3. In the Input Columns tab, select the column you want to pass through the script component.
4. In the Inputs and Outputs tab, add a column with an integer data type. (In this sample below, I have added a column name Column.)

5. Next, go to the Script tab and click the Design Script button. Type the following script in the script editor:

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 intCounter As Integer = 0 'Set intial value here
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
         Row.Column = intCounter
         intCounter = intCounter + 1   ' Set the incremental value here
    End Sub
End Class

In the above script you can set the initial value and the incremental value to whatever you want. In the above example both are set to 1.

6. The following is the output of the script in which the column named Column is the auto incremental field generated from the Script Component.

Comments:

Your Name

(Emails will not be displayed on the site or used for promotional purposes)

Comment

Type characters in the image

(case sensitive)