Skip to main content


Generating Surrogate Keys in SSIS

 As we know, in MSSQL, we could set column identity property as yes to get incremental value, and we could also use sql function RowNumber() to get this.

But in SSIS, there is no RowNumber() function, and setting column identity property is also not very flexible, and here is another way that using script to generate.

Here is overall structure:


ST_GET_MAX_EDW_ID :  get maximum EDW_TARGET_ID from target table then assign to a global variable

DF_LOAD_ACCOUNT :  use script in this dataflow to calculate value based on this global variable

Here is detail information:

1. In ‘SQL Task’ ST_GET_MAX_EDW_ID, do below

  • In ‘General’ tab:


               a. Set ‘ResultSet’ with ‘Single row’

               b. Put below select query in ‘SQLStatement’


                                  FROM DBO.ACCOUNT

  • In ‘Result Set’ tab


             a. Assign result value to global variable


2. In Dataflow, before insert into target table, use ‘Script Component’ named ‘Key_Generation’, then

  • In ‘Inputs and Outputs’ tab, create an output column ‘EDW_ACCOUNT_ID’


  • In ‘Script’ tab


             a. Select global variable in ‘ReadOnlVariables’

  • Click ‘Edit Script’ button (use C# as example)

             a. Define local variable in class

                     int intCounter = 0;

             b. Overwrite function Input0_ProcessInputRow by putting below script

               if (intCounter == 0)


                     intCounter = this.Variables.GEDWTARGETID;


              intCounter += 1;

              Row.EDWACCOUNTID = intCounter;


Then you could assign column EDW_ACCOUNT_ID in target table Surrogate Key mapping.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us