December 6, 2008 at 2:04 am
We are using a distributed SSIS transaction for fetching data from one table in a database (DB1), and insert the same data to a table in another database (DB2). We are using a simple data flow, with a OLE DB Source and OLE DB destination. The data flow is set inside a Sequence Container, with "Transaction Required" option.
If we embed the SQL statement for fetching data from the first database into a Stored Procedure and call that SP from the OLE DB Source, we are getting the following exception:
"SSIS Incompatible transaction context was specified for a retained connection"
Interesting thing is that if the statement is written without the data flow (using Execute SQL Tasks), it works.
Please let me know how to resolve this.
December 8, 2008 at 6:54 am
This doesn't solve the issue you are experiencing but why do you need to have this is a distributed transaction when you are only changing data in one database, the destination. If you are simply selecting the data from the source then there's nothing that needs to roll back. And as long as you have a commit batch size of zero set in the ole destination component then the full data set will need to have been copied over successfully as any error prior to that will cause the entire insert to roll back.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply