SSIS transactions with multiple OLE DB commands

Question

I have the following Data Flow task in my SSIS package:

It reads a file from an external vendor that has records with a column "change" that cointains A, C or D for add, change and delete. I have to process these in my SQL Server database. The conditional split checks the value of the change column and sends the row off to the appropriate command. This works fine.

Because the input files sometimes contain errors I would like to make this process transactional (there are 10 of these Data Flow tasks).

If I enable transactions on the SSIS package however the OLE DB commands seem to interfere with each other (they do not operate on the same rows). I get the error:

Error: 2010-02-02 12:21:08.39
   Code: 0xC0202009
   Source: name OLE DB Command 1 [58]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.
 Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0
"  Hresult: 0x80004005  Description: "This operation conflicts with another pend
ing operation on this transaction. The operation failed.".
End Error
Error: 2010-02-02 12:21:08.39
   Code: 0xC004701A
   Source: name SSIS.Pipeline
   Description: component "OLE DB Command 1" (58) failed the pre-execute phase
 and returned error code 0xC0202009.
End Error
Progress: 2010-02-02 12:21:08.39

I have tried any order of the conditional split and all the IsolationLevels on the Transaction settings but nothing seems to work. If I make the conditional split use only 1 of the branches it works fine. How to proceed?

Answer 1

SSIS is so bad it makes me scream - Seriosuly you should revist whether SSIS is the correct tool here

Answer 2

Try replacing SQL Server Destination with a "OLEDB Destination" for the inserts.