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 learn about Import Expert Wizard.
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 the 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 (https://blog.sqlauthority.com),
31 Comments. Leave new
I love you. It saved my life. Thanks!!!
Thank you Dave,
I am a retired db programmer and have am doing something simple for my wife, however, my memory fails me sometimes and I can’t remember syntax and other functions. Your blog is very helpful. It is clear & concise!
Keep up the good work supporting all of us db-ers. Blessings. Bruce Nutting
same issue – I am copying tables using the SQl Server 2008 import and export wizard. In the “select source tables and views” window I change the mapping. I tried both checking and unchecking the box “Enable identity insert” but in both cases columns that had the “Identity specification” = Yes in the source db are copied to the target db with the “Identity specification” = No.
Any idea?
Only way I could work around this issue was to “backup” and “restore”, would love to have used the import/export tool, not sure what I did wrong.
I have never seen this earlier. There should be a reason which I don’t know.
I wasted an hour or two until I read this!! Thanks,
Thanks Dave. Just as a helpful tip: when you have hundreds of table to export/import you don’t need to go table by table to change the property just highlight multiple tables select the same Edit Mappings button and you will be able to change it for all.
Thank you! Could not remember where the setting was to enable Identity Insert.
Welcome!
Verde good! Thanks
Nicely summarized, thank you! Still relevant in Visual Studio 2021(just a small interface change)!