February 28, 2011 at 4:28 pm
I am using a ssis package where my requirement is to turn off the identity property, update the column explicitly with the value coming from a sample file and then turn on the identity property.
Therefore i have used a Execute Sql Task where i have written the script to turn off the identity property (copied from generate sql script while changing the identity property to no in design mode).
Then i have used a DFT where i am using a OLEDB command to update the column. however the oledb command is not allowing me to write the "Update query" as validation failed showing the error message as can not update the identity column.
I have also tried with SP, while compilig I am getting the same error as during validation is getting failed.
Please suggest how to resolve it.
February 28, 2011 at 4:51 pm
You can never update an identity column. Identity insert allows you to insert an explicit value into it, but there is no similar option to allow updates.
To remove the identity property entirely would require dropping the column.
What exactly are you trying to do? Maybe there's another way...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply