Intermittent failure to enlist SSIS package in DTC transaction

5

Our packages are randomly failing with the following error message:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00A. COM error object information is available. Source: "[Some Package Name]" error code: 0x8004D00A Description: "The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction."

This doesn't happen all the time, and re-running the package often succeeds, so I don't think it's a DTC or network config problem. This happens whether or not we're running the package from within Studio or by a SQL Agent Job. In the latter case, the package is running on the same SQL Server instance that it is trying to connect to.

We can see from the logs that the transaction is being started at the correct point - but a couple of seconds later, we get the failure.

Any suggestions gratefully received at this point!

SQL Server Details: 64-bit Enterprise Edition (v9.0.3233) running on Server 2003 64-bit Enterprise (3790). The servers are running E5345 Xeons, with 6GB of RAM.

ssis
msdtc
asked on Stack Overflow Mar 23, 2009 by Dave W

1 Answer

4

We found the answer in the end - there were two independent tasks in the control flow that were establishing connections to the same database. Evidently there is some race condition somewhere that makes it fail. Adding a precedence constraint to force the tasks to be executed in series made the problem go away.

answered on Stack Overflow Jul 1, 2009 by Dave W

User contributions licensed under CC BY-SA 3.0