Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theboyholty
Posting Yak Master
226 Posts |
 Posted - 2011-03-24 : 04:53:57
|
I've created quite a large SSIS package. Its the first one I've ever created. One of the processes is a Sequence Container which pans out as follows:Execute SQL TaskCode is SET IDENTITY_INSERT MarketData_Company_Stage ON Data Flow TaskTransfers data from a table in one server to a table in a different serverExecute SQL task.Code is SET IDENTITY_INSERT MarketData_Company_Stage OFF The problem is that as soon as the package or even the container is executed, SSIS throws up an error related to inserting IDs into the MarketData_Company_Stage table. In reality, by the time it gets to the database insert, the SET IDENTITY_INSERT ON command would have taken care of this, but SSIS doesn't seem to be able to figure this out.Of course, a solution is to run the whole thing as a single Execute SQL Task and code it as SET IDENTITY_INSERT ON, then the INSERT INTO and then SET IDENTITY_INSERT OFF commands (as this works when executed in SSMS) but that doesn't seem like a very elegant solution to me.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-03-24 : 16:38:10
|
| what is the error ?are you in the right database when you are doing this?could it be that an ID in destination table already exists that is in source table?If you don't have the passion to help people, you have no passion |
 |
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2011-03-25 : 12:51:06
|
| Unfortunately I can't re-create the error as I've done it through a SQL task now as a temporary fix but it was a validation error from trying to insert IDs into an identity column. But the whole point I was making was that I had countered this through the IDENTITY_INSERT command run immediately before the INSERT (and I had also truncated the table before importing the data) so in reality the package would truncate the table, set IDENTITY_INSERT to ON and then run the Data Flow task, but in practise, SSIS couldn't recognise that I was doing this and it failed validation becasue all it could see was that I was inserting into an identity column.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-03-25 : 14:54:51
|
| are you doing IDENTITY_INSERT on another table during this process?BOL saysAt any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
Issue with SET IDENTITY_INSERT command in SSIS