Check Point File Usage in SSIS

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 8932 red flag

This artcle will guide you through the steps on how to use the SSIS Check point file.

 Download source code for Check Point File Usage in SSIS


It's always been hapenning that our SSIS packages fail for some reason or the other for file not found, or server down or some foreign characters in file which cannot be processed in the middle of the package execution. And in all of these cases the fix is a small one and we fix them too and we simply rerun the package again through all the tasks again which were already succcessful, there by spending some extra time on executing the completed tasks again, there we feel that if we could just skip the completed task's so that we can save some time all together.

So yes, SSIS provides us with CheckPoint feature that enables us to continue our package execution from the point where it failed. That means we dont have to rerun the completed tasks again. Isn't it interesting topic...

So Lets start and see how it works.

Open Visual Studio and create a new project add a SSIS template name it as CheckPointUsage.dtsx and we will get the package in the design window. Drag drop the script tasks and design as shown below.

Double click on the first script task and click on the Edit Script button to enter into code zone there add just a simple code as below.

Now similarly in the second script task we will intentionally fail the task by returning the failure result.

Now try to execute the package and you will see that task1 is success, task 2 fails, rerun the package again the task1 executes.

Now we will set the checkpoint property to the package.

In enable checkpoint in SSIS for a package we need to set the below properties

  • CheckpointFileName - This uses a comple file path to save the value of package variables and log completed tasks details.
  • CheckpointUsage - This has three options, Never (default), IfExists, or Always. 
    Never indicates that we are not using any Checkpoints in our package. 
    IfExists - This means if there is any checkpoint file set then use it.
  • SaveCheckpoints - This has two options True or False (default being false). In order to enable checkpoint option we need to set this property as true.

Once this is done next what we need to do is to set the FailPackageOnFailure option to true for all the individual tasks in the package.

Incase you have your task inside any container like foreachloop container or sequence container then you also need to set the FailParentOnFailure property to true.

Now the save all the changes and execute the package we will see that the task1 completes successfully with message poping up and task2 failes.

the task two failed as expcted. Now if you see the checkpoint file

Now stop the package and change the code back to return success from the script code editor.

And execute the package again you will see that now the task1 is not executed instead only the task2 gets executed and the package runs successfully.

Thats it..


So we saw how to use the checkpoint feature of SSIS which will really save a lot of time when executing a complex package. Hopw you all like this article. Do post your comments and feedback.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Madhu Rokkam
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/13/2011 3:13:20 PM
Country: India
Thanks and Regards Madhu

Login to vote for this post.

Comments or Responses

Posted by: Tripati_tutu on: 2/22/2011 | Points: 25
Hi Madhu,

Apart from the series of articles on SSIS submitted by Karthik, this one is also good one.

Posted by: Madhu.b.rokkam on: 2/22/2011 | Points: 25
Thanks Tripati for your comments .. Will add more in the comming weeks too
Posted by: Karthikanbarasan on: 2/22/2011 | Points: 25
Good one.... It was under my card, you covered it :)
Posted by: Madhu.b.rokkam on: 2/22/2011 | Points: 25
I read your mind karthik .. :)

Thanks for your comments
Posted by: Karthikanbarasan on: 2/22/2011 | Points: 25
Haha... Good :)

Login to post response

Comment using Facebook(Author doesn't get notification)