SSIS Rollback Package

Question

I have several data flow tasks in my package that depend on other data flow tasks. For testing reasons I want to run the entire package and then roll it back after it completes. How do I do it?

Answer 1

Wrap all the changes in a transaction and then abort it.

How To Use Transactions in SQL Server Integration Services SSIS

Answer 2

If there is no other activity that you need to persist (assumed from the reference to testing) then a database snapshot can be useful here.

There's obviously a tipping point around the volume of change whereby a regular full backup could be faster if you're churning a large percentage of the database. But, as the creation of the snapshot is instantaneous (the mechanism uses a sparse file) and the regular backup route requires you to create the backup first, snapshot is usually the winner for dev/test activity.

Note that reverting from a database snapshot shrinks the transaction log to 0.5MB due to a rather nasty bug, so you'll need to re-size your transaction log appropriately.

Answer 3

Start with a backup, and then restore from that backup on error or as the last step. The backup and restore could be steps in a job, with the SSIS package being a step between. That way, when you've got everything else worked out the way you want, the SSIS package can be used to make and keep the data changes you want.