Professional SQL Server™ 2005 Integration Services

13.4. Checkpoints

SSIS has a feature called Checkpoints that has absolutely nothing to do with the definition that probably came to mind when you read the section title. In SQL Server, a checkpoint occurs when the data is written to disk. In SSIS, you can enable a checkpoint on a package to allow a failed package to restart at the point of failure.

Say you have a package that does a lot of processing, but one task or container in the package is likely to fail occasionally. That task might require a connection to an Internet Web service, or another resource must be available. Maybe you really don't want to repeat all the heavy lifting that the package does before the failure. By configuring a checkpoint, the package can be restarted at the failed task. Information about the package as it runs is saved into a checkpoint file, which is then used when the package is restarted.

To learn more about using checkpoints, create a new SSIS project in BIDS. The package in this example will not do anything particularly useful, but it will illustrate the use of checkpoints. Add four Script tasks to the design area. Connect them as shown in Figure 13-20.

Figure 13.20. Figure 13-20

Click the Control Flow design area and open the Properties window of the package. In the Checkpoints section of the package Properties window, enter a path for the checkpoint file. Set SaveCheckpoints to True. These ...