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.