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?

3 Answers 3


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

How To Use Transactions in SQL Server Integration Services SSIS


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.