Why do you use both, the TransactionOption set to Required, and also involve MS DTC? I would use one or the other thing. You choice. PS: I also do not see why and how it is applicable to use IsolationLevel set to Serializable?Arthur My Blog

Thanks for your comments. The first thing I tried was setting the TransactionOption to Required [the IsolationLevel is defaulted to Serializable and I am happy with that level of concurrency]. However, first test run failed. Search for resolution to error message showed that MS DTC was needed if you want to use transactions. Does SSIS support reading an Oracle source using using an OLE DB Source and writing to MS SQL using an OLE DB Destination within a transaction. On the face of it, the answer is no? Thanks...Andrew

Hi Going round and round without success. Trying to incrementally copy data from a set of Oracle 10g tables into some SQL Server 2008 tables. Have the solution working without any transaction control. However, no matter what I try, I cannot get it to work with transaction control. I have manufactured a test that fails in the last data flow; and I need the previously inserted rows to be rolled back (removed). Attempt 1 was to put my Data Flows into a sequence, and then set the TransactionOption to Required with an IsolationLevel as Serializable. Each Data Flow has its TransactionOption set to Supported and Serializable. I have enabled the MS DTC as documented, ie Security Configuration: Enabled Network DTC Access, then Allow Remote Clients, Allow Inbound, Allow Outbound, Enable TIP. Someone suggested changing Mutual Authentication to Incoming Caller Authentication Required - this has had no effect. I have also tried enabled Enable XA Transactions also without effect. As soon as it runs into the first Data Flow the Output reports Information: 0x40043006 at SourceA Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning. Error: 0xC001402C at RRA To ODS, Connection manager "dora.RRA": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.". Error: 0xC0202009 at RRA To ODS, Connection manager "dora.RRA": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00A. The connection manager mentioned above is the Oracle Connection. I have tried both the MSDAORA.1 Provider and the OraOLEDB.Oracle.1 Provider. Both fail the same. The RetainSameConnection similarly makes no difference between false or true. My guess is I need something similar to MSDTC for the Oracle connection, but cannot find any information about this. Attempt 2 was to set the container to Supported - this too failed, same reason. Attempt 3 was to set the container to Not Supported. Back working, but no rollback on failure not working in the previously successful data flows. Attempt 4 was to introduce specific transaction control using Execute SQL Command. Introduced this as the first control with "begin tran mytran" as the sql command and then a conditional (Failure) "rollback tran mytran" and a conditional (Success) "commit tran mytran". These were done on the Destination (SQL Server) Connection. Failure did not cause a rollback. Attempt 5 was same as Attempt 4, however, I changed the RetainSameConnection to true. Now a different error emerges, specifically Error: 0xC001A004 at RRA To ODS, Connection manager "Transacted MELWDDODS01\D_ODS_01.ODS": Incompatible transaction context was specified for a retained connection. This connection has been established under a different transaction context. Retained connections can be used under exactly one transaction context. Please, can anyone help? Cheers...Andrew

Hi Further update. Interestingly, I de-configured MSDTC and am getting the same result with it started or stopped - a failure to enlist the Oracle connection whenever the TransactionOption is Required or Supported. I must not have picked up on the connection when trying to resolve yesterday and just thought it changed from the target to the source. Any ideas anyone? Thanks