I recently got an email from an old friend who told me that when he tries to execute the SSIS package, it fails because of some identity error. After a few series of debugging and opening his package, we finally figured out that he has the following problem.
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)
To solve this issue, we enabled the checkbox as seen in the above image. This way, we were able to fix the problem.
The reason why he was facing this error is because his destination table had the IDENTITY property which will not allow any kind of insert from the user. This value is automatically generated by the system when new values are inserted in the table. However, when the user manually tries to insert a certain value in the table, it this property stops the action and shows an error.
Enabling the property “Enable Identity Insert” by checking the checkbox allows the values to be inserted in the identity field. This way, the exact identity values are moved from source database to destination table.
Let me know if this blog post is easy to understand, so I can further elaborate the details as necessary.
Reference: Pinal Dave (http://blog.SQLAuthority.com),