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

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 10152 red flag
Rating: 5 out of 5  
 1 vote(s)

This is part 10 of the series of article on SSIS


 Download source code for 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

SSIS Articles Part 1 to 6

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)