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’

                    SELECT ISNULL(MAX(EDW_ACCOUNT_ID), 0) MAX_EDW_ACCOUNT_ID

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

                                  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.