SQLTeam.com | Weblogs | Forums

Ssis transaction not working properly


I have only one connection and the RetainSameConnection = True for that connection. I am using the same connection in all my data flow tasks. Here is my package step by step:

Execute SQL task with statement begins transaction

I have 10 data flow task all using the same connection in a sequence container. Transaction option is supported for all dataflow task as well as for sequence container.

I have 2 SQL task for one failure and one for success

success: Execute SQL task with the statement commit transaction using the same connection.

failure: Execute SQL task with statement rollback transaction

Commit is working fine.

But Rollback is behaving weird. So I have 3 tables A, B, C and tables B and C have A as the source. At the end of the transaction when there is an error, though there is nothing in table A, table B and C is already populated. So somehow table B and C are not the part of this transaction.

I hope I was able to explain it clearly.