A couple of months ago I did a post on Transactions in SSIS, but that solution requires enabling the windows service Microsoft Distributed Transaction Coordinator (MS DTC). What if you can't (or prefer not to) use that service?
Solution
You can use the Transact SQL transactions to accomplish the same result. Same example as before. I want to empty and refill a table with values from a CSV file, but I want to keep the old data when the refill fails. My package:
 |
| Example |
1) Container
Add a Sequence Container and drag the existing Execute SQL Task (which empties the table) and the Data Flow Task (which fills the table) to it.
 |
| Sequence Container |
2) Start Transaction
Add an Execute SQL Task before the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: BEGIN TRAN MyTran.
 |
| Start Transaction |
 |
| BEGIN TRAN |
3) Commit Transaction
Add an Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: COMMIT TRAN MyTran.
 |
| Commit Transaction |
 |
| COMMIT TRAN |
4) Rollback Transaction
Add an other Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: ROLLBACK TRAN MyTran.
 |
| Rollback Transaction |
 |
| ROLLBACK TRAN |
5) Precedence Contraint
Change the Value property of the Precedence Contraint between the Sequence Container and the Rollback from Success to Failure. If something fails in the Sequence Container the Rollback command will be executed.
 |
| Precedence Contraint |
 |
| Failure |
6) RetainSameConnection
Now the most important thing. Change the RetainSameConnection property of the database connection from false to True.
 |
| RetainSameConnection |
7) The Result
That's all there is. Now you can test your package. You can open the CSV file in Excel to lock the file and fail the package.
 |
| The Result |
* UPDATE *
Added the optional transactionname in case you want to re-execute. See comment Arthur Zubarev.