This is the first part of a series on using checkpoints in SSIS. In this article, I'll look at the basics of checkpoints, including enabling and configuring them in their simplest form. Future articles will cover more advanced usages of checkpoints.

Note: this article applies to SQL Server 2005.

What are checkpoints?

SQL Server Integrated Services (SSIS) offers the ability to restart failed packages from the point of failure without having to rerun the entire package.  When checkpoints are configured, the values of package variables as well as a list of tasks that have completed successfully are written to the checkpoint file as XML.  When the package is restarted, this file is used to restore the state of the package to what it was when the package failed.

Enabling checkpoints

There are 3 package-level properties that need to be set in order to enable checkpoints.

• CheckpointFileName– Specify the full path to the checkpoint XML file.  This file will be automatically created when checkpoints are enabled.
• CheckpointUsage– Indicates whether checkpoints are used. IfExists indicates that the checkpoint file should be used if it exists and is the most common setting used.  Always indicates that the checkpoint file must always exist or the package will fail.
• SaveCheckpoints– Indicates whether the package saves checkpoints. This value must be set to True in order for packages to restart from the point of failure.

Setting these 3 package-level properties will enable checkpoint functionality in a package, but by default, no tasks are setup to log checkpoints. For each task and container in the package that you want to identify as a restart point, you must set the FailPackageOnFailure property True.  By default, this property is set to False, so you’ll need to remember to change it after adding new tasks to the package. 

Verifying checkpoints

I added two Script Task objects to my package and set the FailPackageOnFailure property on each task to True.  In order to verify that the checkpoints are setup properly and working as expected, I have set the ForceExecutionResult property on Task 2 to Failure.

This is a fast and easy way to force a task to fail so that you can verify checkpoints.  Don’t forget to change this property back to its default value of None once you have verified checkpoints are working as expected, unless, for some reason, you have a need for a task to fail once it goes to production.

When the package is executed, Task 1 will succeed and a checkpoint will be written with its task identifier, whereas Task 2 will fail and no checkpoint will be written to the file.

You’ll now need to determine what caused the task to fail.  It could be that there is an issue with the underlying data that’s referenced by the task and you just need to clean up the data.  There might also be a problem with the task itself, in which case you’ll need to resolve the problem by opening the package and making changes to the task.  Once the cause of the failure has been resolved, the package can restarted.

In my case, I know that the task failed because I forced it to, so I just need to change the ForceExecutionResult property on Task 2 back to the default value of None.

Since a checkpoint was written for Task 1, this step will be skipped and the package will restart on Task 2.

If the cause of the failure was resolved, Task 2 will now succeed and the checkpoint file will be updated.

Since all tasks in the package have now completed successfully, the checkpoint file will automatically be deleted.  The next time the package is executed, SSIS will look for the checkpoint file to determine whether it needs to start from the beginning of the package or at a particular task.  Since the file was deleted upon successful completion of the package, a new file is created and execution starts from the beginning of the package.

Conclusion

This article is intended to show you the basics of checkpoints in SSIS.  Here are some of the key points from this article.

• Checkpoints files do not track the status of parallel tasks/containers.
• Checkpoints are only available within the control flow, not the data flow.
• The most common value for the CheckpointUsage property is IfExists.  Rarely, if ever, will you set this property to Always.
• The easiest way to verify checkpoints are working properly is to add a script task and set the ForceExecutionResult property to Failure.