Home > SSIS Best Practices > SSIS – CheckPoint File

SSIS – CheckPoint File

There are many discussions about Checkpoint Restart feature in SSIS.

What does CheckPoint 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.

Sounds wonderful. Right?

What are the steps to configure your package to use it?

Step 1: Checkpoints are enabled at package property level. Before I set the CheckPoint properties, I created 2 variables in the package scope, one for the CheckPoint file path, another one for CheckPoint file name. This is necessary because I don’t want to hard code the file path or name in my package.

In the Variable window, create two variables. The CheckPoint file that is created by the Integration Services is a XML file, so feel free to use xml as file extension. I am developing this package on my local PC, I believe both the file path and file name must exist on my PC. I used Notepad to create an empty xml file. On the target server, only the path needs to be created prior to the execution of the SSIS package. The xml file itself will be created automatically by the integration services.

image

Step 2: Now Enable the following 3 properties at the package properties level

image

For the CheckPointFileName, do not hard code it, instead, use the Expression.

image

You need to open the Expressions, click on the … button. In eh Property Expression Editor, select CheckPointFileName as Property, then click the … button to go to the Expression Builder window.

image

In the Expression Builder window, you can drag any variables into the Expression edit box to build your expressions. Click Evaluate Expression to see if your expression is correct.

image

Once you are done with these 3 package properties, save your package. The expression for the CheckPointFileName will be evaluated at this point and will appear:

image

Step 3: Set the FailPackageOnFailure property to True for each container, and each task if you want them to participate in the CheckPoint restarting process. This property is False by default.

If your task is inside a container, I believe you need to set the FailPackageOnFailure property to True for both the container and the task.

image

Step 4: Test it by setting the ForceExecutionResult property to True for one of the tasks.

This is a quick way to see if the checkpoint file is created or not. Don’t forget to set it back to None after the test.

image

I set this property to True for the second task in my package. The 2nd task still got executed, but it failed at the end of the execution. Verified that the checkpoint xml file is created.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: