SSIS Package Checkpoints

Sometimes a package will fail part way through, and it can be a problem to execute the whole package again. It is possible to edit the package and execute the individual components, but this may not be desirable if there are many tasks within the package.

SSIS has a feature which allows the package to resume from the point at which it failed, called checkpoints.

To get check points to work there are four steps to take:

The first 3 involve the package properties.

1) Set a checkpoint file name

2) Set checkpoint usage to IfExists

3) Set savecheckpoints to True

Then, for each of the tasks that you would wish to be able to resume from, edit their properties and set Fail Package on Error to true.

The next time the package is run, a checkpoint file is created. If the package completes successfully, the checkpoint file is deleted. However, if there is a failure, the package can be executed again, and it will resume  from the checkpoint.

While the package is being executed, you can see the checkpoint file appear, it is a text file, so it is human readable.

If, one time, you do not wish the package to resume from the checkpoint next time it is run, simply delete the checkpoint file. The next time the SSIS package is run, it will start from the begining.