Using Checkpoints and Restarting Failed Packages

Prior to SQL Server 2005, one of the more challenging tasks to accomplish was providing for “restartability” in packages. Ideally, packages that take a lot of time or resources should be able to restart at the point they failed (because of a power outage, for example). This could be accomplished in DTS, but it took a lot of extra effort.

SSIS makes it easy to restart a package from the point of failure using a feature called checkpoints. After a minor bit of package configuration to enable checkpoints, any time a package fails, it will write an XML file to disk that tells SSIS where to restart if execution is tried again.

You’ll modify your original package to use checkpoints in Exercise 6.2.

Exercise 6.2: Configuring Checkpoints

In this exercise, you’ll configure some checkpoints:

1. Start by right-clicking in the Control Flow area, selecting Properties, finding CheckPointFileName, and setting it to package.xml. Note that you can name the file whatever you want; we just find it useful to have the .xml extension so that we can easily view the file if needed. You should also make sure that the SSIS service account has access to the folder where you place the file.

2. Next you need to set the property called CheckPointUsage. It defaults to Never, which tells SSIS to ignore the checkpoint file and run as if the package had not failed, effectively disabling the feature. Setting it to IfExists means that if the file is present when the package runs, SSIS will parse the file and automatically resume execution at the previous failure point. The final option is Always, which requires the checkpoint file to be present to run, which is a fairly specialized situation! In this exercise, you’ll set your package to use IfExists.

3. Now scroll down a bit, and set SaveCheckPoint to true. This takes care of the high-level work needed to enable checkpoints.

4. Now you’ll alter your control flow so that you can simulate a failure in your export page. Drag a script task onto the Control Flow tab and wire it to your Data Flow task (ours is named Export Contact Table).

5. The script task won’t really be doing anything, but think of it as doing some really complicated work that takes a long time. The final step in setting up your checkpoints is to open the properties for your Data Flow task and set FailPackageOnFailure to True. Any error in the Data Flow task will cause the entire package to fail and will trigger the creation of your package.xml file.

6. Now you need to force an error. You can cheat just a little by setting the ForcedExecutionResult property of the data flow to failure. Now when you run the package, you can see that it fails, and looking at the Progress tab, you can see your package.xml referenced.

   

7. Reset the ForcedExecutionResult property of the data flow task to None, and then try running the package again. It might not show up very well here in print because of the lack of color, but you should see that the Script task doesn’t turn green or red. It stays white because we skipped execution of it based on the checkpoint file.

   

8. If you take another look at the Progress tab, you can confirm the checkpoint restart.

   

The only “gotcha” to remember is that checkpoints can be set only on tasks in the control flow, not in the data flow.

Real You Need Scenario-Do You Need Checkpoints?

Let’s say that the first step in your package is to retrieve data from each of 100 retail locations. Retrieving data from a single store might take as long as three minutes (dial-up connections!), and getting the data from all stores could easily take two hours. Once you have the data, the rest of your package executes fairly quickly, completing in about 20 minutes. You have a four-hour window to complete the process.

Without some type of restartability, if your package fails anywhere-including that last INSERT statement that logs completion-you’ll be forced to run the entire package again, including the two hours of downloads, and you will not complete processing in the four-hour window specified.

For this basic scenario, you can see that you definitely want a checkpoint at the end of the entire download process so that if something happens later, you won’t have to burn up that dial-up connection again. Note that in practice you might set up checkpoints after every location is processed so that if one store fails, you can just restart from that point rather than reprocessing all stores.