SSIS Usage of Checkpoint File

If you are an ETL application developer or administrator, one problem you may have faced is rerunning the entire ETL application in the event of failure. Rerunning the application from the point of failure is often not possible due to many reasons, but mainly due to the difficulty in implementing logging and maintaining each step of the application and configuring the execution based on the available log. SQL Server Integration Services provides a mechanism to automate the restarting process from the point of failure up to some extent. This mechanism has been provided using Checkpoint files. It helps us configure the ETL solution's execution flow in the event of a previous execution failure. This article explains the usage of checkpoint files with some tasks available with Integration Services.

This article Includes:

• Setting up the checkpoint file
• Checkpoint usage with for loop
• Checkpoint usage with events
• Other constraints

Setting up the checkpoint file

The configuration of the checkpoint file starts with setting up three properties of the package; CheckpointFileName, CheckpointUsage, SaveCheckpoints. These properties are available with the package properties, under Checkpoints category (See the properties with categorized view). CheckpointFileName allows us to set the path of the checkpoint file. Since the info is stored in an XML format, giving an xml extension to the file would be better though it is not required (eg. D:\Checkpoint Files/LoadDataPackage.xml).

The second property, CheckpointUsage tells Integration Services whether this package requires a checkpoint file or not. Three values are accepted; Never, IfExists and Always. Never indicates that the checkpoint file should not be used and execution of the package must be started (or restarted after failure) from the beginning. Always requires the checkpoint file for every execution. Once set to Always, the package will not be start without a checkpoint file. IfExists instructs the package to use the checkpoint file if it exists only. IfExists is the best setting for CheckpointUsage because the checkpoint file will exist only if the package ends in failure. Remember that once the checkpoint setting is set, the package creates the checkpoint file at the given location during execution and then erases it on successful execution. The checkpoint file will only remain if the package ends in a failure.

SaveChekpoints indicates whether the package needs to save checkpoints. It accepts either true or false. This needs to be set to true if the package needs to be restarted from the point of failure.

The last step is, configuring each task, specifying the involvement in the checkpoints. This is done through the property called FailPackageOnFailure. This property needs to be set to True for enabling the task in the checkpoint.

Okay, let's set this up. Open Business Intelligence Development Studio and start a new Integration Services project. Add two File System Tasks. Create three variables for holding two physical paths for two text files and another path for destination. Name the variables as illustrated below. You may change the path according to your folder structure.

Note that, since this demo shows the implementation of checkpoints, transforming and transferring have not been implemented, hence they are represented with two Script Tasks that just pop-up messages saying "Records successfully saved!". Now create two text file as Transaction1.txt and Transaction2.txt. Do not copy them to E:\Clients\Files\. Now Configure File System Tasks like below:

Add messages to both the Script Tasks too. Now the package is done. Note that we have NOT configured anything related to the checkpoint. First we run the package without setting it, and understand the problem, then try to solve the problem with checkpoint.