http://www.sqlservercentral.com/blogs/dknight/2009/06/18/enabling-checkpoints-in-your-ssis-packages/

Printed 2014/07/30 05:30PM

Enabling Checkpoints in your SSIS Packages

2009/06/18

Checkpoints are a great tool in SSIS that many developers go years without even experimenting with. I hope to enlighten you on what Checkpoints are and why it is beneficial to use them. Also, I will walk you through a basic example package where they have been implemented.

What does it do?

With Checkpoints enabled on a package it will save the state of the package as it moves through each step or task and place it in a XML file upon failure of the package. If your package does fail you can correct the problem in your package and rerun from the point of the tasks that did not successfully run the first time. Once the package completes successfully the file is no longer needed and automatically discarded.

How does this benefit you?

Just imagine your package is loading a table with 10 million records. Your package passes the Data Flow that performs this huge load without any problem (Other than the fact that it took two hours to load). The next task in your package is a Send Mail Task and for some reason fails.

You correct the problem in the Send Mail Task, but without using Checkpoints your package would still have to run that Data Flow that loads the 10 million records again (taking another two hours) even though you’ve already done it once. If you had enable Checkpoints on this package you could simply correct the problem in the Send Mail Task and then run the package again starting at the Send Mail Task. Sounds great right?

How do I configure it?

This example will run you through very basic package using Checkpoints.  To see these instructions with screenshots go to my regular blog http://blogs.pragmaticworks.com/devin_knight/2009/06/enabling-checkpoints-in-your-ssis-packages.html

Example Overview

Step 1: Configure Execute SQL Tasks

Step 2: Configure Package to enable Checkpoints

Step 3: Configure Each Task

Step 4: Run the Package

Step 5: Correct the Problem and Rerun Package


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.