Oracle Transaction under MS SSIS

Question

Hi, I'm trying to get transactions working under SSIS (SQL Server Integration Services 2005) and Oracle with no luck. I'm using Oracle 10g.

I have created a Sequence Container, set its TransactionOption to "Required" and put inside it a DataFlow Task with TransactionOption = "Supported".

The server running SSIS has the MSDTC service running.

The DataFlow Task has, as source, a SQL Server OLEDB connection and as target an Oracle OLEDB connection.

When trying to execute it, I get the following errors:

Error: 0xC001402C at MediaMarketAnnullamento, Connection manager "ITMASORASVIL:1521/SVILUPPO.EDP": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D01B "The Transaction Manager is not available.".

Error: 0xC0202009 at MediaMarketAnnullamento, Connection manager "ITMASORASVIL:1521/SVILUPPO.EDP": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D01B.

Error: 0xC020801C at 9999 - Insert_MAGAZZZINO_MM_SHOW, MAGAZZINO_MM_SHOW_SSIS [2187]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ITMASORASVIL:1521/SVILUPPO.EDP" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC004701A at 9999 - Insert_MAGAZZZINO_MM_SHOW, DTS.Pipeline: component "MAGAZZINO_MM_SHOW_SSIS" (2187) failed the pre-execute phase and returned error code 0xC020801C.

Information: 0x40043009 at 9999 - Insert_MAGAZZZINO_MM_SHOW, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at 9999 - Insert_MAGAZZZINO_MM_SHOW, DTS.Pipeline: "component "MAGAZZINO_MM_SHOW_SSIS" (2187)" wrote 0 rows.

Task failed: 9999 - Insert_MAGAZZZINO_MM_SHOW

Information: 0x4001100C at 9999 - Insert_MAGAZZZINO_MM_SHOW: Aborting the current distributed transaction.

Information: 0x4001100C at Insert su Oracle: Aborting the current distributed transaction.

SSIS package "MediaMarketAnnullamento.dtsx" finished: Failure.

Any help? Thanks

EDIT: I just discovered that the server running Oracle is not, as I thought, Windows based but IBM ICS. I don't know if this is relevant.

Answer 1

Looks like it may be a problem with MSDTC permissions this MS blog post might help to debug it. Also I'm assuming that the permissions on the Oracle server are set correctly see this MS kb article for help with this.

Answer 2

Is this doc of any use?

http://msdn.microsoft.com/en-us/library/aa344212.aspx