Error:The connection does not support enlisting in distributed transaction.

  • Article

When we use the Microsoft OLEDB provider for DB2 from the Feature Pack for Microsoft SQL Server 2005 in SSIS connection manager and set the component level or package level Transaction option to Required. The package execution fails with the error below:

[Connection manager "ConnectionName"] Error:The connection does not support enlisting in distributed transaction.

 

CAUSE
=====

The standalone version of the Microsoft OLEDB Provider for DB2 does not support distributed transactions, as per the documentation:

Topic: Configuring a Data Source for OLE DB Provider for DB2

Distributed transactions: This property is disabled in the Microsoft OLE DB Provider for DB2 that is available for use in conjunction with Microsoft SQL Server 2005, but is enabled with the full version of Host Integration Server 2006

Resolution
=======

We need to install the Full version of Host Integration Server (HIS) on the SSIS computer and use the OLEDB Provider for DB2 that is part of HIS 2006

To configure the Connection Manager to support transactions, please follow the steps below:

1. Install HIS 

2. Open Business Intelligence Development Studio 

3. Create a Connection Manager for DB2, while configuring the connection manager Click on ALL

4. Under the section DB2oledb set the property "Units of work" to "DUW"

5. Now you can set the component and package level transaction option to required and it will be supported.

Reference:

https://support.microsoft.com/kb/218590

https://msdn.microsoft.com/en-us/library/aa492163.aspx

 

Chetan Rao
SE, Microsoft Sql Server.