Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Checkpoints in SSIS

By Dinesh Asanka,

Introduction

SQL Server Integration Services (SSIS) packages are designed to import data from different data sources like databases, flat files and FTP locations etc. Some packages may take several hours to execute depending on the implementation. In middle of the package execution, in case the FTP location is not available or the source text file is missing, execution will fail. Most of the times, you need to attend to the relevant issue and re-run the package. This means that package will run from the beginning and which means that you have to wait again for long time.

In simple terms, you are wasting your times by re-running the entire package. If you have an option where you can restart the package from the point of failure it will be easier.

The Checkpoint feature in SSIS is about restarting package from the point of failure.

Configuration

The technique behind checkpoint is to save the variables values at the time of failure to a file in a XML format. When it re-run, it reads from the saved file and load the variables and will start from the failed flows. When the package completes successfully, the Checkpoint file is removed; the next time the package runs it starts executing from the beginning since there will be no Checkpoint file. When a package fails, the Checkpoint file remains on disk and can be used the next time the package is executed to restore the values of package variables and restart at the point of failure.

First you need to configure the checkpoint file name. For this you need to right-click the package and select properties.

In the properties window, navigate to Checkpoint category and configure like following image.

CheckpointFileName is path and name of the file in which you are going to save the check point values. You can have any extension you like. However, it will be better to have a extension such as .xml since the checkpoint file is an XML file. I have also seen that some people are using .CHKP as the extension. The point that I want to stress is that you can have any extension name you want for your checkpoint file name. Also, rather than hard-coding your file name it is better to use some kind of strategy for the file names. The following strategy is the recommended method that I used and it is working fine up to now.

1. Have a common folder like C:\checkpointfiles to save all the package configuration files in your SSIS project. In case you need to restart from the start, you simply need to delete the file in the above folder.

2. Pass the folder name to a variable in the child packages from the parent package by using parent package variable type in the package configuration. We have already discussed how to create package configurations for SSIS packages. By following above two steps, by simply changing the main package variable which contains the value for checkpoint folder, all the locations will be changed.

3. The best way to assign a name for package is to assign the package name to the checkpoint file name as there can be only one checkpoint file for a package. For this you can use an expression in the package properties which will be like following.

4. By using the @[System::PackageName] variable, you can address the issues of renaming SSIS packages.

CheckpointUsage is to determine how checkpoints are used. There are three options available, they are Never (default), IfExists, or Always.

Never indicates that you are not using Checkpoints and in case of a re-run the failed package from the beginning.

IfExists is the typical setting and implements the restart at the point of failure behaviour. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task.

The Always choice raises an error if the Checkpoint file does not exist.

SaveCheckpoints should be True to implement the Checkpoint behaviour. If this is False it doesn't matter what you have configured previously. It should be the first option to select in the checkpoint configuration.

After configuring package for the checkpoint configuration, next is to configure each control task

For each control flow task, you need to set True for FailPackageOnFailure and FailParentOnFailure properties.

FailPackageOnFailure should set to True so that the SSIS package fails if this task fails; this implements the restart at the point of failure behaviour when the SSIS package property SaveCheckpoints is True and CheckpointFileUsage is IfExists. If you have not configured this to true then package will return success, though this task is failed hence checkpoint configuration will not be effected.

FailParentOnFailure Select True when the task is inside of a container task such as the Sequence container; set FailPackageOnFailure for the task to False; set FailPackageOnFailure for the container to True.

Demo

Having understood the theory behind checkpoint, now it is time to show an implementation with checkpoint. I have created simple SSIS package to demonstrate the SSIS Checkpoint. In this there are three Execute SQL Tasks.

Task A: Select 1/1

Task B: Select 1/0 - Which will introduce an error

Task C: Select 1/4

After configure the SSIS checkpoint, let us run the package and you will end-up with following state.

Let us check the checkpoint file at this moment.

You can see that checkpoint file will save all the variables along the with value time at which the package was failed. Then it will save the ID(s) of the controls that was successful. In the give example {7C89E7ED-4225-44CB-949F-0FA797D0238E} is the ID of the Task A.

Then fix the error at the Task B and re-run the package and you will see the following state for the SSIS package.

Important thing to note is that, it will start from the failed task earlier. As it does not run the failed task, it will reduce the run time. After the successful completion of the SSIS package, checkpoint file will be deleted.

Important Notes

There are two important points to remember when it comes to SSIS checkpoint.

1. Checkpoint can be implemented only at the control flow not to the data flow. This means that in case of failure in middle of the data flow, at the re-run of package it data flow will run from the beginning.

2. Checkpoint will not have transaction enable. For example, if there is a task which has several Insert statements and in case of a failure of the one insert statement, other insert statement will not be rollback. In case of a re-run of the package, failed control will execute again and there will be duplicate of data. Therefore it is advisable to introduce transaction to SSIS packages.

Total article views: 9559 | Views in the last 30 days: 8
 
Related Articles
FORUM

Package configurations

Package configurations

FORUM

ssis package configuration

dynamic package configuration

BLOG

Enabling Checkpoints in your SSIS Packages

Checkpoints are a great tool in SSIS that many developers go years without even experimenting with. ...

ARTICLE

Using Checkpoints in SSIS (Part 1)

SSIS is a great platform for building ETL type applications with SQL Server. One of the great featur...

FORUM

Disable property in SSIS package Configurations

package configurations

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones