MSDTC is frequently required when using transactions within SSIS. And not just when you are connecting to two SQL Server instances.

Disclaimer: Neither SSIS nor MSDTC are my strongest skills. So while the information here is correct (to the best of my knowledge) I won’t make any claims as to it’s completeness.

tl;dr; The MSDTC service has to be not only turned on, but configured on all of the machines involved. Including the machine running the SSIS package (possibly a workstation).

So first what is MSDTC? MSDTC is Microsoft’s Distributed Transaction Coordinator service. It’s used to coordinate multiple data sources within a single transaction.

Next, you were aware that you can have transactions within an SSIS package correct? It’s pretty simple. You can follow the link for instructions.

To the best of my knowledge if you create an SSIS package you need to have MSDTC enabled if more than one machine is involved. The most obvious occurance of this is when you actually have multiple data sources. But it can also happen when the SSIS package is being run on a machine that is not one of the data sources. For example, running the package from a workstation and connecting to a test instance (that isn’t on the workstation).

Seems fairly obvious right? Well one of my co-workers set up one of these SSIS packages and was 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.

“The Transaction Manager is not available.” Well that was rather odd since we have MSDTC running on all our SQL Servers. After a week or so of dead ends I realized I was stuck. As you may know once I’m stuck I follow my 20 minute rule. Well, maybe a bit longer than 20 minutes. Basically if I’m stuck I go looking for help. So since this wasn’t a question I could pose in 140 characters
I posted my problem on SE. Over the next few weeks I was given several pieces of advice and finally tracked down an answer.

MSDTC had to be turned on on the workstation (or application server) that SSIS was running from. That’s easy enough. Just turn on the service right? I did that, and then got the following 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.

The important part of the error being The transaction manager has disabled its support for remote/network transactions. That got me quite a bit farther. I finally realized that I hadn’t configured my local MSDTC! I can’t be certain that this is the minimum configuration to make SSIS transactions work but it worked for me.

Using hindsight this seems pretty self explainitory but it certainly wasn’t at the time.

Filed under: Distributed Transaction Coordinator, Microsoft SQL Server, SQLServerPedia Syndication, SSIS