If you experiment at all with transactions that are built into SSIS you will discover that they are highly flawed. For example, if you have a transaction running on an entire package some tasks may not rollback on a failure. A file system task is one of the major culprits that on a failure will not rollback the file operation it is performing. I wish I was writing to give you a solution to that problem today but I’m actually writing to show how you could use a typical database transaction across multiple tasks in SSIS.
To demonstrate this I designed the package below. The #1 Execute SQL Task starts the transaction and #3 commits it. It sounds good but when I run this package the value of 2 is still inserted into my table.
To make this work how you would think it should you simply need to change one property in the connection manager properties. Select the connection manager used in these tasks and change the property called RetainSameConnection to true.
When you run the package again no values are inserted. This is exactly what I wanted.
