ISSUE#0002:0x8004D01B " The Transaction Manager is not available.". The DTC transaction failed to start.

There're some TransactionOption can be set in SSIS Package (Package Level, Container Level, Task Level...):

• Required - if a transaction exists join it else start a new one
• Supported - if a transaction exists join it (this is the default)
• NotSupported - do not join an existing transaction

But sometime when you set Required to a container or other component and to execute the package, error information will show you:

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.

Just use an example to show how to use Trasaction in SSIS package and solve this issue.

1. There're two SQL Tasks in a container, the first task is to TRUNCATE all data in 'Documents' table, the second task is to insert one record to table 'Documents'. I just use a wrong insert statement in the second task, so the second task will throw an error when I execute this package. We use default TransactionOption - Supported to all components.

 

The result is the 'Insert Data' task failed, but the 'Truncate Data' works and all data in table have been deleted.



2. Change TransactionOption of Sequence Container to 'Required'.

 

 



Execute this package and get this error

 



 

The error information show - 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.

3. This error information tells you need to start your MSDTC Service. Open the cmd prompt with Administrator role.

Try 'NET START MSDTC' to start your MSDTC Service.

Then execute this package again.

 

Task 'Insert Data' still throw an error but the 'Truncate Data' will be roll back. Because they're in a same transaction when Sequence Container set its TransactionOption from 'Supported' to 'Required'.