Using Sql Server Integrations Services (SSIS) with Identity Columns

What do you do when you have to transfer data into an Identity column when you already have the identity values filled in the source? Do you? 

a) change the schema to remove the identity column, transfer the data and then restore the schema

b) leave the identity column out of the insert query and let the Sql Server fill in the values. (not good when there are gaps)

c) use a script component

d) write insert statements that turn on IDENTITY_INSERT beforehand.

A. None of the above. Just check off the Keep Identity checkbox in the Connection Manager tab.

Image

 

Thanks to http://www.bidn.com/blogs/MikeDavis for his blog article: http://www.bidn.com/blogs/mikedavis/ssis/161/using-ssis-to-insert-data-into-a-table-with-an-identity-column

Advertisements
Tagged with:
Posted in Development
One comment on “Using Sql Server Integrations Services (SSIS) with Identity Columns
  1. Mike Bishop says:

    Actually, the correct answer is “It depends”. If you are using the OLE DB provider for the connection, then your answer above is exactly right.
    But, if you are using the ADO.NET provider, you have a different “right” answer.

    You do have to use ‘SET IDENTITY_INSERT’ commands in the Execute SQL Tasks surrounding your Data Flow task.

    You do have to set the RetainSameConnection to True on the property page of the (ADO.NET) Connection Manager.

    Finally, in the Data Flow task, you *must* uncheck Use Bulk Insert when possible on the ADO.NET Destination Connection Manager Editor.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: