Configure DTC for distributed transactions

This post describes how to configure the Distributed Transaction coordinator to allow  distributed transactions.

The issue arose developing an SSIS package. The SSIS package consisted of two Execute SQL tasks within the same container. Each task updates a different SQL Server. (fig 1 below).

fig 1 – SSIS package

Initially, when the container Transaction option property is set to “not supported” the package ran successfully.

However, the system specification requires that both SQL operations either complete or fail. To enable this, the containers “TransactionOption” property is set to Required (fig 2 below).

fig 2 – set the transactionOption

After setting the TransactionOption property, the package fails with the following error:

Error: 0xC001401A at 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.

After checking that the MSDTC service is running, I amended the MSDTC settings as follows:

Run dcomcnfg from the start menu. There are other ways to begin, but this is common to most versions of Windows.

Expand the Component Services – Computers – My computer – Distributed Transaction Coordinator nodes as shown in fig 3 below.

fig 3 – dcomcnfg

Right-click on Local DTC, select properties and move to the Security Tab. In the Security Settings, check Network DTC access and Allow Outbound (as in fig 4 below).

fig 4 – DTC security tab

Click OK. This will re-start the MSDTC service.