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),
Pinal can you include more business intelligence stuff in you blog. hope you will cover the next new orleans microsoft bi sessions in your blog.
Thanks, it worked.
Thanks. This is a useful piece of information
Thanks . it was a great help.
thanks,
i have done this (batch copy table) with error at the end of it, but forgot what i could do to solve it, and found your blog, it really helps thanks!
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?
It is not working……… I have 600 mb file and it is stuck on “54 remaining”..
SO?
Is there a way to insert rows into an existing table (with an identity field) and generate the next unique number for the identity table. i.e. last unique key in destination table = 501, data to import needs to continue 502, 503, etc.
Is using a cursor the best way to achieve this?
NB: I’m using SQL 2k5
Hi,
I have one query. Though it is not much technical but i want to this. I want this check box you are talking about (Enable Identity) to be checked by default. Is there any way to make this by default. Actually i do have identity in all of my tables. Every time i do transfer data, i have to check this. Is there any help?
Regards,
Hii Pinal,
consider that i have more than 500 tables then what to do?any way to do for all in one time.
Hi pinal,
I need to update a existing sq table column from an excel file using sql import wizard.pls reply me to
[...] on System – Real World Scenario Negative Identity Seed Value and Negative Increment Interval Enable Identity Insert – Import Expert Wizard Denali – SEQUENCE is not [...]
I am having the same problem as German above. I check Enable identity Insert and run the import, then check the imported table and the Identity is set to no.
Pinal my man
Same issue as Nikunj, I have hundreds of table and can do an import, but it would take me days to set each identity individually!
How can we do it for all tables??
Your help would be appreciated. Loving your work.
[...] Enable Identity Insert – Import Expert Wizard 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. [...]
I really like the way you explain things. It really helps a lot.
just used your tip thanks!