How do I do distributed transactions with DB2 z/OS as target?

[dalecook]

I have a simple (one table) SSIS package that has DB2 z/OS as the target. When the "Generate CDC Packages" wizard created the package the components' "TransactionOption" properties were set to "Unsupported". The package runs successfully this way, but I want the CDC package to be processed as a logical (distributed) unit of work, so I set the components' "TransactionOption" properties to "Required". When I run the package, the first component, "Initialize Change Processing Task" returns the following error:

[Initialize Change Processing Task] Error: Failed to acquire Target connection. Microsoft.SqlServer.Dts.Runtime.DtsGenericExceptio n: Error HRESULT E_FAIL has been returned from a call to a COM component. ---> System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Runtime.ConnectionManager. AcquireConnection(Object txn) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.ConnectionManager. AcquireConnection(Object txn) at Attunity.SqlServer.Dts.Tasks.CDCLaunchTask.Acquire Connections(Connections connections, Object transaction, ConnectionManager& sourceConnectionManager, ConnectionManager& targetConnectionManager)

What do I do? I've created a couple of other packages that use a remote SQL Server as a target, and I noted that the CDC package wizard by default created all the components using TransactionOption set to "Required", which was good, and they run fine, and I can see DTC handling the transaction.

Help?

Host: DB2 z/OS 9.1
Client: Windows Server 2003 Enterprise x64 SP2
SQL Server 2005 Enterprise SP3
DB2 Connect 9.5 fp4
Attunity DB2 z/OS CDC for SSIS 2.1.14 x64

[Gadi.Farhat]

Hi,

Seems like the DB2 OLEDB provider is failing to enlist in a distributed transaction. Check the DB2 OLEDB connection manager properties. What is the "Units of work" property value?


Regards,

Gadi

[dalecook]

There is no OLE DB driver being used. The package is the "Default_CDC" one that your CDC Package Builder Wizard builds for CDC processing, so it uses your ADO.NET components. However, the error I reported above doesn't happen on the CDC Data Flow component, but on the "Initialize Change Processing Task".

Since there doesn't seem to be something obvious I'm missing, I'll open a ticket.

[Gadi.Farhat]

Hi,

The package builder wizard creates a Connection Manager (CDC ADO.NET Target DB Connection) that is used by the CDC package to connect to the target database, in your case DB2 z/OS.

The Target Connection Manager uses the OLEDB provider selected in the Package builder. You mentioned that you are using DB2 Connect 9.5 therefore, i assume you selected the DB2 OLEDB provider.

The Initialize Change Processing Task in the CDC package acquires a connection from the Target DB Connection Manager. In your case it failed.

You may Double Click the "CDC ADO.NET Target DB Connection" connection manager and select the "All" page to view/edit the OLEDB properties.


Regards,

Gadi