SQL SERVER – Enable Identity Insert – Import Expert Wizard

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)

SQL SERVER - Enable Identity Insert - Import Expert Wizard enableindentityon

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),

, ,
Previous Post
SQL SERVER – Difference Between GRANT and WITH GRANT
Next Post
SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

Related Posts

30 Comments. Leave new

  • I love you. It saved my life. Thanks!!!

    Reply
  • Bruce Nutting
    April 6, 2016 10:02 pm

    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

    Reply
  • 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.

    Reply
  • I wasted an hour or two until I read this!! Thanks,

    Reply
  • Eduardo Del Castillo
    November 29, 2017 11:43 pm

    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.

    Reply
  • Thank you! Could not remember where the setting was to enable Identity Insert.

    Reply
  • Verde good! Thanks

    Reply

Leave a Reply

Menu