SET IDENTITY_INSERT tableName ON isn't turning ON

Question

SET IDENTITY_INSERT tableName ON isn't turning ON

larry.powell

SSC Veteran

Points: 248
More actions
October 14, 2011 at 9:59 am

#244725

I have a couple of tables in a SSIS package data load that have identity attributes. I have added the sql
SET IDENTITY_INSERT dbo.InspectionFrequency ON as a stand alone execute sql task in the package and I've also had it in a stored procedure as part of a table preparation process. But when ever I run the package I get a message "Cannot insert explicit value for identity column in table 'InspectionFrequency' when IDENTITY_INSERT is set to OFF.
".
What gives?
I've tried surrounding each of the table loads with it's own SET IDENTITY_INSERT dbo.InspectionFrequency ON then after the dataload SET IDENTITY_INSERT dbo.InspectionFrequency OFF but nothing seems to work. I did this similar process in SQL 2000 using stored procedures and or DTS SQL task in the past. Any help is appreciated.
Thanks,
Larry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The only thing I have in mind is that you're limited to only 1 table at a time for this setting. So maybe something else is left set to on.

However I would expect you to get an error message somewhere.

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 2

I am second to Ninja as set IDENTITY_INSERT works for a single table at a time and before make it on for other table you have to off it for previous table.

sturner SSC-Insane Points: 22411 More actions · Answer 3

IDENTITY_INSERT is a session setting so if your script (or DTS package) is issuing the commands in separate sessions like this:

exec('SET IDENTITY_INSERT dbo.InspectionFrequency ON');

exec('Insert dbo.InspectionFrequency ( < column_list> ) values( <column_list> )');

exec('SET IDENTITY_INSERT dbo.InspectionFrequency OFF');

it may not work like you want it to. Can you post your script? It would work like this:

exec('SET IDENTITY_INSERT dbo.InspectionFrequency ON; Insert dbo.InspectionFrequency ( < column_list> ) values( <column_list> ); SET IDENTITY_INSERT dbo.InspectionFrequency OFF;');

The probability of survival is inversely proportional to the angle of arrival.

larry.powell SSC Veteran Points: 248 More actions · Answer 4

Thanks to both of you. I decide to just add stored procedures to encapsulate the logic.

All is well.

Thanks