Expert SQL Server™ 2005 Integration Services

7.4. Transactions

Checkpoint files are only the first tool in your SSIS arsenal to help with restartability and those middle-of-the-night calls. A more important problem is managing data state. For example, if you were to run a package that loads three tables and the third table was to fail, ideally, you would want all three tables to roll back. Otherwise, you may not know what state your data is in to fix the problem and rerun the package.

Transactions in SSIS allow you to protect your data and envelope any data change that occurs in the package in that protection. Transactions can be either on the entire package, a container, or an individual task. They can be nested just like in TSQL. What is important to note about transactions is that only data-related tasks are protected. If your package were to archive a file, for example, that file would not be un-archived upon a package failure (unless you explicitly created some kind of compensating action to perform such duties).

Let's take the previous package you created and wrap the package in a transaction. First, let's disable the checkpoint from the previous example by changing the CheckpointUsage package property to Never and the SaveCheckpoints property to False. Next, delete all the records from the RestartabilityExample table.

With the example now reset back to its original state, you're ready to enable transactions on the package. In the Properties window for the package, change the TransactionOption at the bottom of the ...