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

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 settings will cause the package to save information as it executes to the checkpoint file. Set the CheckpointUsage property to IfExists. If the package fails, it will use the information in the checkpoint file to restart execution at the point of failure. If the package completes, the file will be deleted afterward.

Select Script Task 2 and open the Properties window. Change the ForceExecutionResult property to Failure. This will cause the task to fail, regardless of any other conditions. In this example, the setting will be used to simulate a failure of the task.

If you run the package now, Script Task 2 will fail and Script Task 3 will not get a chance to execute. The package, however, will complete and the checkpoint file will be deleted. If you rerun the package, it will start at the first task. To get this to work as expected, the package must actually fail when Script Task 2 fails. Change the FailPackageOnFailure property of Script Task 2 to True in the Properties window of the task.

Go ahead and run the package. Once again, Script Task 2 should fail (see Figure 13-21). This time, however, the checkpoint file will still be available.

Figure 13-21

Go back to design mode and change the ForceExecutionResult property of Script Task 2 to None. Rerun the package. Package execution will start at Script Task 2 (see Figure 13-22), picking up where it left off from the previous run.

Figure 13-22

Any task or container can be configured to be a checkpoint by setting the FailPackageOnFailure property to True. With clever use of checkpoints, you have the chance to correct a problem and restart the process from the point of failure instead of from the beginning. But how will you know what caused the failure? Read on to learn about another feature, Logging, for the answer.