Sql Server 2005 SSIS unable to enlist SYBASE distributed transaction

Question

Good morning,

I know very little (arguably nothing) about SYBASE setup, but I do know SSIS is having trouble enlisting SYBASE in a distributed transaction. Has anyone been able to make this work?

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x80004005 "Unspecified error".

This happens when I change the package's TransactionOption to Required. When I revert to the default "Supported", the package runs without errors (albeit not thread safe).

Thanks, Alan.

Answer 1

I had this same problem when I tried to create a transaction around a read from a Gupta SQLBase. Basically, it seems that SSIS (at least as of 2005) isn't able to enroll any other providers in a transaction as part of a package. I've tried a few times without luck, and usually I just end up reading the data from my OLEDB into a temporary table, and then creating a transaction around the import of that data into its resting place in SQL Server. That's the read side, though - if you're trying to use a transaction to write to SYBASE, you'll need to do something on that side - SSIS won't be able to use a transaction to push data to another provider.

I addition to that, I didn't even want my transaction to extend to Gupta - I only wanted to enroll my INSERT/UPDATE on the SQL Server side in a transaction, to block users from reading half-updated data, but SSIS refused to allow me to wrap the process in a transaction because Gupta didn't support it. There seems to be no support for transactions just on certain providers, or only on the "Write" side of the process but not on the "Read" side.