I've got an SSIS package that I've been trying to adapt to use distributed transactions. Whenever I set the TransactionOption to "Required" in the outermost container, everything stopped working. The only way I could get any SQL steps to work would be to change the TransactionOption to "NotSupported", which kind of defeats the purpose. :)
The error I get from my OLE DB Source is:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECT
AGER. The AcquireConnection method call to the connection manager "MyConnection" failed with error code 0xC001A004. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
The Connection Manager is using OLE DB and I have RetainSameConnection set to True.
There is a linked server involved and I do have DTC set up on both machines. I've tested the connection between them using DTCPing and all tests were successful.
Could anyone suggest things I could try? I suspect it's something to do with the settings of DTC.
See attached file for current DTC settings.