I recently got email from old friend who told me that when he tries to execute SSIS package it fails with some identity error. After some debugging and opening his package we figure out that he has following issue.

Let us see what kind of set up he had on his package.

Source Table with Identity column
Destination Table with Identity column
Following checkbox was disabled in Import Expert Wizard (as per the image below)

What did we do is we enabled the checkbox described as above and we fixed the problem he was having due to insertion in identity column.

The reason he was facing this error because his destination table had IDENTITY property which will not allow any  insert from user. This value is automatically generated by system when new values are inserted in the table. However, when user manually tries to insert value in the table, it stops them and throws an error.

As we enabled the checkbox “Enable Identity Insert”, this feature allowed the values to be insert in the identity field and this way from source database exact identity values were moved to destination table.

Let me know if this blog post was easy to understand.

Reference: Pinal Dave (http://blog.SQLAuthority.com),

Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology