Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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

How To Use Transactions in SQL Server Integration Services SSIS

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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