SQL Server Integration Services (SSIS) – Checkpoints in SSIS Packages

Introduction

In this article we will look into the use of checkpoints in a SSIS package. Checkpoints are nothing but a structure for restarting the package at the point where it fails without having to restart from the beginnig. This feature is an added advantage of SSIS packaging which provides better performance with complex tasks. A checkpoint saves the configuration details in a XML file which acts as the source for the later execution section. When the package has been restarted the default point is restored by the checkpoint by referring to this XML file only.

Checkpoint configuration is by default false in SSIS; we need to manually configure in order to use this feature. We will see here how to configure and use the checkpoints feature in SSIS packaging. Before enabling the checkpoints we need to know the properties available with checkpoints in order to use it effectively. It has 3 main properties as shown below.

·         CheckpointFileName - Automatically created XML file for configuration

·         CheckpointUsage- Shows if the checkpoint is in use or not

·         SaveCheckpoints - Shows if the checkpoints saves or not in the packaging.

Let's jump into the step by step process on how to configure checkpoints and how to use them for our packages.

Steps

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see how to configure checkpoints.

I have created a project here which has 2 tasks, both the tasks returns a positive response as success. At this point we will not see the properties and the tasks are executed perfectly as shown in the following screen

 

In order to enable the checkpoints we will make the second task a negative task and try to run the project. It will display as shown in the following screen.

Now we can see the Check point properties in the property window of the package list as shown in the following screen. Here we have configured to save the checkpoint and to use it.

Now the checkpoints are configured and to use them make the negative response to respond as positive and run the package again and see what happens.

Conclusion

So in this section we have seen the usage of checkpoints and how to make the configuration and how to use the checkpoint as per the requirements.