Implementing checkpoints in SSIS

You can configure a package to start from the point of failure when the package is rerun. In SSIS, this configuration process is called checkpoints.

• Restart failed packages from the point of failure.

Some of the uses of Checkpoints are 

1.Avoid repeating the downloading and uploading of large files. For example, a package that downloads multiple large files by using an FTP task for each download can be restarted after the downloading of  a single file fails and then download only that file.

2.Avoid repeating the loading of large amounts of data. For example, a package that performs bulk inserts into dimension tables in a data warehouse using a different Bulk Insert task for each dimension can be restarted if the insertion fails for one dimension table, and only that dimension will be reloaded.

3.Avoid repeating the aggregation of values. For example, a package that computes many aggregates, such as averages and sums, using a separate Data Flow task to perform each aggregation, can be restarted after computing an aggregation fails and only that aggregation will be recomputed.

Checkpoint file

• Checkpoint file stores the information about package execution.
• The current values of variables are also captured in the checkpoint file. 
• Integration Services captures the restart point in the checkpoint file.

Configuring Checkpoints

To implement checkpoints in your package, you must configure several properties at the package level:

• CheckpointFileName: 

              Specifies the full path and filename of your checkpoint file.

• CheckpointUsage: 

             Specifies when to use checkpoints. The property supports the following three options:

1.Never: A checkpoint file is not used.

2.IfExists: A checkpoint file is used if one exists.This option is the one most commonly used if enabling checkpoints on a file.

3.Always: A checkpoint file must always be used. If a file doesn't exist, the package fails.

• SaveCheckpoints: 

            Specifies whether the package saves checkpoints. Set to True to enable checkpoints on the package.

• FailPackageOnFailure property to True

Properties

1.CheckpointFileName

2.CheckpointUsage

3.SaveCheckpoints

4.FailPackageOnFailure