I recently received an error while trying to run a SSIS package in Visual Studio. The full error is seen as below.
Error: 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.".
This error is happening when a component with transactions try to connect and manipulate data on a remote server.
To avoid this error, two things can be done.
* Remove the transaction handling from the component. – This should be done only if the transactions are not required or as a temporary measure.
You can do this by selecting the properties of the component, also check the package properties to make sure transactions are not enabled at the package level.
For an explanation of the options please visit the URL.
* Configure the Distributed Transaction Coordinator (DTC).
For a component to work correctly with transactions, below two things are required.
1. Running Distributed Transaction Coordinator (DTC) Service
This service should be running on the database server the component connects to and on the machine where the package is running. This will coordinate the work between two machines / servers.
This can be checked and started by going to Windows Services.
2. Properly configuring the security of DTC.
To make the DTC service work properly, it should be given permission as required. This can be done by opening the Component Services window. In the component services window, navigate to Component Services –> Computers –> My Computer –> Distributed Transaction Coordinator. Right click Local DTC and click on Properties to open properties window.
If you are intending to run any workloads with transactions via any external servers you should enable “Network DTC Access”.
Depending on the situation you should allow Inbound, Outbound or both transaction manager communication options.
For complete list of explanations on each property please refer the following URL.