Why does the SSIS Runtime fail to start the distributed transaction?

Question

While creating an SSIS package for SQL Server 2008 i run into the following error:

Error: 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.

I'm creating and running the package locally on my machine but the database is on a server, running Windows Server 2008, which is not in the domain.

I have made sure that the DTC service is started both locally and on the server, and I have added the Firewall exceptions that are predefined in the Windows Vista firewall.

Why does the SSIS Runtime fail to start the distributed transaction?

Leblanc

See also: https://mcmap.net/q/327005/-msdtc-on-server-39-server-is-unavailable-39/155892 In our case it was one of those stupid 3rd party services interfering – Disaccredit

Answer 1

I had the same problem, however, MS DTC was not running on my machine. To turn on the Transaction Coordinator I had to do the following:

To start MS DTC

1. To open Services, on the Start menu, click Control Panel.
2. In Control Panel, click Administrative Tools.
3. In Administrative Tools, click Services. In the details pane, click Distributed Transaction Coordinator in the list of services.
4. On the Action menu, click Start.

Aeolic

This is the second time i've googled and came right back to this post which solved my issue. Does anyone know why it happens? Or how do I make sure to stop it in the future? – Pervert

Answer 2

Check my solution to this problem here [ http://faiz.kera.la/2009/08/26/ssis-transaction-enabled-tasks-fail-due-to-msdtc ]

This is a common scenario if your machines are not in a domain or running Windows XP.

Edit: the link is dead. The original text from the link seems to be:

In my current project we have multiple SSIS developers in team and we all were sharing the database server instance in my system. We faced an issue yesterday when a team mate was trying to implement transactions in SSIS. The package fails in other machines although it is running smooth in my system where the database resides. The error message thrown out was,

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E “The transaction has already been implicitly or explicitly committed or aborted”

Soon we realized that this is something related to Microsoft Distributed Transaction Coordinator (MsDTC). We did some search and got a tool called “Dtcping.exe” which will check the health status of MsDTC processes in different machines. The tool reported an error “Access denied”, hinting some security issue with in the MsDTC. But we were not lucky even after a couple of hours of Googling. Then I decided to lean the security settings for MsDTC and I found that all network related connectivity is disabled by default. I learned that authentication settings can cause trouble as our machines were running Windows XP and as they were in a work group (we have a strange network configuration in my organization). I changed to “No Authentication Required” for MsDTC instances in all machines and it worked! To change the Security Configuration for MsDTC, go to Control Panel >> Administrative Tools >> Component Services >> Computers >> Right click My Computer and then click Properties >> Click the MSDTC tab >> Click Security Configuration. Below is a screen shot of the settings that I used, but I do not recommend this configuration for all cases as I am not aware of the impact it can have on securiy.

Seismograph

Thanks Andrew for the efforts. My blog is long dead. I have added a pic I just downloaded from net which serves the same purpose. – Seismograph

Answer 3

To start the Distributed Transaction Coordinator service on Windows 11, follow these steps:

Open Services:

Click on the Start button or press the Windows key. Type Services in the search bar. Click on the Services app that appears in the search results. Locate Distributed Transaction Coordinator:

In the Services window, scroll down to find Distributed Transaction Coordinator. Start the Service:

Right-click on Distributed Transaction Coordinator. Select Start from the context menu.

Nicknickel