15

I'm working on a package that needs to use a transaction but I'm currently getting the following error:

SSIS package "CATS-Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001401A at CATS-Package: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
SSIS package "CATS-Package.dtsx" finished: Failure.

Here is what I know so far:

  • 2012 package
  • I'm running in package deployment
  • The TransactionOption property is set to Required
  • Running against a 2008 R2 instance
  • I can create a distributed transaction manually on the instance and acording to the event viewer MSDTC is running.
  • MSDTC has the following settings

enter image description here

Can anyone point in a direction to find additional information.

Edit: One of the steps I read said to run DTC on the local machine running the package as well as the server holding the instance. Once I did that I got a new error:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

With the important part being The transaction manager has disabled its support for remote/network transactions. Am I missing something in the permissions? Note: the Allow Remote Clients has also been checked.

I've seen MSDTC ping mentioned (including in the comments below) but when I looked at it I'm not quite sure what to do with it.

Improve this question
12
  • 1
    Found more detailed info that actually shows some troubleshooting steps for DTC and SSIS: richardlees.blogspot.com/2010/01/…
    – user507
    Commented Dec 2, 2015 at 1:23
  • 1
    What method are you using to set your transaction in the SSIS package?
    – Zane
    Commented Dec 11, 2015 at 20:21
  • 1
    Check the application event log for any errors from msdtc, get MSDTC ping support.microsoft.com/en-us/kb/918331
    – Spörri
    Commented Dec 16, 2015 at 9:21
  • @Spörri I didn't see anything in the event log on the client or server. I've looked at MSDTC but it looks (on first glance) over my head.
    – Kenneth Fisher
    Commented Dec 16, 2015 at 19:06
  • 1
    @KennethFisher I did just mean servername in the powershell test, if you got info back then RPC is open.
    – Bob Klimes
    Commented Dec 17, 2015 at 17:10

1 Answer 1

Reset to default
12
+150

I solved the problem. I had read in several places that DTC needs to be started on the source machine as well as the destination. So in my place my workstation as well as the server the instance is on.

Once I had turned MSDTC on I received a new error:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

With the important part being The transaction manager has disabled its support for remote/network transactions.

Once there I did additional research and finally realized I hadn't configured my local DTC to allow Network Access etc. Once I configured it to allow Network DTC Access, Allow Remote Connections and Allow Output connections everything worked smoothly.

enter image description here

I'm not sure this is the minimum permissions required for the local connection but it appears to be.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.