SQL Server Integration Services (SSIS) – Part 10 – Check Points in SSIS Packages

Introduction

In this article we will look into the Check point’s usage in SSIS package. Check points are nothing but a structure where we can restart the package at the point where it fails without having to restart from the first step. This feature is an added advantage for SSIS packaging which provides a better performance in order to achieve complex tasks. Check point saves the configuration details in a XML file which acts as the source for the later execution section. The package once restarted the default point is restored by the check points by referring to this xml file only.

Check point configuration is by default false in SSIS, we need to manually configure in order to use this feature. We will see here on how to configure and use the check points feature in SSIS packaging. Before enabling the check points we need to know the properties available with check points 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 check point is in Use or not

·         SaveCheckpoints – Shows if the check points saves or not in the packaging.

Let’s jump into the step by step process on how to configure check points and how to use it for our packages. To follow my series of articles on SSIS packages refer to the below URL

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 on how to configure Check points.

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 below screen

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

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

Now the check points are configured and in order to use it now make the negative response to respond as positive and run the package again and see how it going to take it.

Conclusion

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