Saturday, January 26, 2013

SQL2008R2 Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.


While I’m trying to implement transaction handling in SSIS packages i.e  Setting isolation level is ‘Serializable’  and Transaction option into ‘Required’ in the Sequence container properties,



 Error is coming as like below.
[Connection manager "SSIS Package 2008R2"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction."

Solution:
1. Put all the tasks inside the sequence container as per the below screenshot and execute the SSIS package, it will run successfully.


2. Make sure you are executing the package in the server itself not from the local host or other server.
Hope above will helps.!!!!!!!!!

4 comments:

  1. The easier solution is to set the MaxConcurrentExecutables on the package properties to 1. This makes it execute a single process at a time and avoids the error. Still not ideal as one of the benefits of the SSIS package is being able to run things concurrently.

    Similar solution discussed here:
    http://stackoverflow.com/questions/674364/intermittent-failure-to-enlist-ssis-package-in-dtc-transaction

    ReplyDelete
    Replies
    1. I tried that also and it did not work for me.

      Delete
  2. Hi Jcrawfor74,

    Thanks for the comments and alternative method...

    ReplyDelete
  3. All the components in my package are connected in series. But I still get this error.

    ReplyDelete

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...