Inserting Records and Getting Identity in SSIS

Hi ,

When one of my friend asked about how can we catch a Identity value after inserting the rows in to table  having a Identity column and using that result in the same package

well there is no out of box solution in SSIS  once the data is fed in to the OLEDB destination or SQL server destination it becomes a impossible to find inserted Identity values (Unless a trigger is used on the destination table) for these components outputs are ERROR redirection, which we can not use

Problem :

During the Loading phase of ETL it is so common to have identity columns , but it becomes to tough to find the Identity values after inserting and using them in the same Data flow

Solutions :

There are many work around solutions to perform this which I will list them , I will try to demonstrate the one way which I like

1) Using trigger on the destination table catching the inserted values in another table

2) Using the OLEDB command and a stored procedure which uses Output clause to find all Identity values or we can use Identity_scope (problem: dataflow will be terminated which makes the procedure to be the control flow of data (which makes no sense of using SSIS))

Famous MVP Todd Mcdermid  tries to explain the same problem in his view point please take a look

3)add a unique number to a comment field to find the rows just inserted and in other data flow task use the unique number to process them as required

As it is a RBAR(row by agonizing row) which is time consuming and it degrades the package performance when the number of rows are more

4)  Using the Record-set destination and taking the rows back to the control flow , and using the for each loop enumerator and catch the inserted Identity columns and use the values for further processing in SSIS

lets look at the example

In this session we will also learn how to use  Recordset destination

How to perform cusrsor action in Dataflow

how to use Foreach loop (Foreach ADO enumerator)

how to transfer data between control flow and dataflow

so looks like nice information session on SSIS so please stay with me


One thought on “Inserting Records and Getting Identity in SSIS

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s