Hi,

I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented. My workflow is as followed

*********************************

For Each ADO.Record in Oracle (transaction=not supported)

If (Certain_Field_Value = 'A')

Lookup Data in SQL DB with values from Oracle (transaction=not supported)

DO Sequence A (Start a Transaction , transaction=required)

INSERT/UPDATE some records in SQLDB(transaction=supported)

Finish Sequence A ( transaction should stop here)

UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)

If (Certain_Field_Value = 'B')

Lookup Data in SQL DB with values from Oracle (transaction=not supported)

DO Sequence B (Start a Transaction , transaction = required)

INSERT/UPDATE some records in SQLDB (transaction=supported)

Finish Sequence A ( transaction should stop here)

UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)

If (Certain_Field_Value = 'C')

------------

------------

End ForEach Loop

*************************************

My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.

But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is  ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".

What is it the I am doing wrong?

Regards

KyawAM