January 21, 2009 at 4:48 am
I am having a problem with copying data (all of it) from a selection of tables in a SQL 2000 database to a SQL 2005 database in SSIS using the "Transfer SQL Objects" Task. It first warns that the tables exist (which is fine), then proceeds to truncate them. After which it sits there for an age before failing with the error ;
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "SET IDENTITY_INSERT [dbo].[tablename] ON" failed with the following error: "Table 'dbo.tablename' does not have the identity property. Cannot perform SET operation.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Both tables have identity columns.
I am choosing to copy and replace the data without dropping objects first. I am only using objects that belong to dbo in 2000 and in any case the CopySchema option does not work.
I have read many posts regarding problems with this task (and its quite scary how bug-riddled this task has become), but none related to SSIS requiring Identity columns in order to set this option ON.
Any help would be appreciated.
January 21, 2009 at 6:30 am
Actually I think I might know why it doesn't work. Funny how writing (posting) the problem down makes it a bit clearer in your own mind.
I originally thought there were no identity columns. Actually, both the source and destination have the same structure and field names including an identity column :D.
After reading the error message again, I looked closer at the fields in both locations.
The destination table does not have the identity property set whereas the source does! Now, this is a task that has been migrated from DTS to SSIS so this must have been a requirement in 2000 (DTS). In 2005 (SSIS) it seems now to allow for the identity property being set in the destination.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply