Checkpoints in SSIS

The Control Flow in Integration Services gives you control and an overview of the tasks that run in your package. But when one of these tasks fails, you often do not want the previous steps to run again. Maybe you ran something that must not run 2 times (an INSERT or DELETE script, for example), or the previous steps take a few hours. The solution for this is Checkpoints.

What is it?

Checkpoints in SSIS keep track of where your package got to in its run. While your package runs, SSIS keeps track of the status of your variables and tasks and of what is already done. If your package then fails, SSIS knows where it got to, so the next time the package runs it can continue where it stopped.

How do I turn it on?

In Integration Services you turn on Checkpoints in the package properties at Control Flow level.

The first thing to fill in here is the CheckpointFilename of the checkpoint. We do not fill it in here, though: we build it dynamically with expressions.
The CheckpointUsage must be set to IfExists. This means that SSIS uses the existing Checkpoint if there is one, and otherwise creates a new file
The last setting asks whether the Checkpoints must be saved physically. We set this to True, so the Checkpoint is physically there to use.

CheckpointFileName and Expressions

As we see, the CheckpointFilename is an xml file, and you can generate it dynamically with expressions. The main reason for this is that you can then turn this option on by default in a template package that you use as the basis for SSIS development.

Before we can use the expression, we first create a variable with the path where the file must go. We do this by creating a String variable and writing the standard path into it.

After that we use this variable in an expression together with the System::PackageName variable, so the Checkpoint gets a clear (and unique) name. The property CheckpointFileName is the one that gets the expression.

The end result

When we have done all this, an XML file with the status of the package appears in the folder you set (the variable) while the package runs.
This file keeps track of the values of the variables and of which Control Flow items are already finished. With this data, SSIS can start the package at the point where it went wrong, with all the variable data it needs.

By the way, Checkpoints work perfectly together with Transactions in SSIS. I will explain how that works in a later post.