I'm having trouble implementing transactions on a simple SSIS package. The package has the following components: 1. Execute SQL Task to delete all records in the destination table 2. Data Flow task that (a) connects to SQL Server data source (b) does some lookups (c) inserts all records to SQL Server destination The package runs fine with all TransactionOptions set to 'supported' If I add a sequence container, or set TransactionOption of the package to 'Required', I get a failure at step 2 (a). The error is [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "dbSource" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. If I set TransactionOption of the data flow to Not Supported, the package runs to Step 2 (c) and just sits there...after 10 minutes or so I stopped it. This is SSIS 2008 64 bit, running on Windows 2008 Server. The source datbase is an external SQL Server 2005, the destination is the local instance of 2008R2. Thanks in advance, John

You likely don't have MSDTC enabled. http://lanestechblog.blogspot.com/2010/04/utilizing-transactions-in-ssis-to.html

Thanks for the reply. You are correct that I did not have MSDTC enabled. The article mentions that if you are running SSIS on the destination server, which I am, that this is not required. I enabled it anyhow, and have the same results, failure at step 2 (a). So, I'm still stuck! I'd appreciate any further advice. Thanks, John

Ah, I think you may mean setting up the DTC on the source server. Makes sense. I'll test and update this thread. Thanks.

I do not think you need to go the MSDTC route, the error above simply perhaps due to the fact you are sharing connections!Arthur My Blog

I do not think you need to go the MSDTC route, the error above simply perhaps due to the fact you are sharing connections! Arthur My Blog I'm not sure what you mean by sharing connections? The data source and destination have separate connections. Also, when the package is run without using transactions, it works fine. Also, when I run the package using DTEXEC, I see this additional error: Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions."

I mean this: How to Use Transactions in SSIS: http://www.mssqltips.com/tip.asp?tip=1585Arthur My Blog

Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions." If you are seeing this error its either a firewall issue or MSDTC is not enabled on all the Machines enlisted in the transaction. Reread the original document I supplied :-)