SSIS Checkpoint

•          Checkpoints enable a failed SSIS package to be restarted at the task where the execution was ended.

•          Checkpoint is used to avoid repeating the downloading and uploading of large files or to avoid repeating the tasks that consumes system resources heavily.

•          Checkpoints are enabled by setting the package’s SaveCheckpoints property to True in the SSIS package properties.

•          Once checkpoints are enabled, you also need to tell the SSIS package where to write the checkpoint data. To do this, you must supply a filename to the CheckpointFileName property.

•          In addition, the way SSIS treats running packages where there is an existing checkpoint file is controlled by the CheckpointUsage property. Available option for this property are Never, Always and IfExists.

 

Let us understand the behavior of checkpoint by an example:

Create a new package in SSIS project and change the SaveCheckpoints property to True and CheckpointFileName property to “C:\SSIS_checkpoint.xml”. Add three “Execute SQL Tasks”. The statements and control flow is as below:

Task A: Select 1/1

Task B: Select 1/0 - Which will introduce an error

Task C: Select 1/4

Now execute the SSIS package. It will failed at Task B as below:

Now update SQL statement of the Task B to Task C: Select 1/2 and re-execute the package. This time package will start at Task B as below: