I have a SSIS Package which is Having a Dataflow task in a Container,which Transaction Option is "Required" and the Dataflow task Transaction Option is "Supported" for both Isolation Level "ReadUncommitted" . SSIS exec successfully, if I have both Source & Traget DB in the local system and its rolling backs all the transaction in the case of error.
But if i changed my Target to different DB (in Prod i have to refer difference Server/DBs) SSIS is failing with following error(s).
[OLE DB Command INSERT INTO Generic_Answers 1 ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TargetDb1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "OLE DB Command INSERT INTO Generic_Answers 1" (4255) failed the pre-execute phase and returned error code 0xC020801C.
Earlier I was using the SQL Transaction in my SSIS (using Exec SQL Task) but this also failed, because the Multiple Insert Stmts across various Tables is causing Exclusive lock on table(s).
past 2,3 weeks I was googling, and i did all MSDTC and all related configs. But still i got stuck in the begin stage itself. Please help me on this issue, if any one got any kind of solution....