Skip to main content

Development

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:

 01

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:

                02     

               a. Set ‘ResultSet’ with ‘Single row’

               b. Put below select query in ‘SQLStatement’

                    SELECT ISNULL(MAX(EDW_ACCOUNT_ID), 0) MAX_EDW_ACCOUNT_ID

                                  FROM DBO.ACCOUNT

  • In ‘Result Set’ tab

                03

             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’

             04

  • In ‘Script’ tab

             05   

             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
TwitterLinkedinFacebookYoutubeInstagram