Using Checkpoint Restarts

If a package contains components that require a lot of resources to execute, you might not want to rerun the entire package when a single component fails. Checkpoints enable SSIS to restart failed packages from the point of failure instead of rerunning the whole package. If a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package is run successfully, the checkpoint file is deleted and then re-created the next time the package is run.

Understanding the Benefits of Checkpoints

Using checkpoints can provide the following benefits:

• Avoid repeating the downloading and uploading of large files.

• Avoid repeating the loading of large amounts of data.

• Avoid repeating the aggregation of values.

When using checkpoints, entire containers or tasks are re-executed. For example, if a Foreach loop fails on the third item of its enumerator, when it restarts, it starts on the first item of the enumerator. If a file contains a faulty record and causes an error, the whole file is read when package execution resumes.

Configuring Packages for Checkpoints

A package must be configured to provide checkpoint functionality. This entails the following:

• Configuring the package to save checkpoints.

• Giving the package the location of the checkpoint file.

• Configuring checkpoint usage. Always means the package will not execute unless a checkpoint file is available. IfExists means the package will use a checkpoint file if it exists; otherwise, the entire package will execute.

Individual checkpoints are configured by setting a task or container's FailPackageOnFailure property to True. If a container is configured as a checkpoint, all tasks within the container will execute when the package is restarted.