Check points in SSIS with Example

Introduction

        In this article we will explain about Check point’s usage in SSIS. The configuration of check points helps us to resume from the last task in the package. Check point saves the configuration details in a XML file which acts the source for later execution section. For example if multiple tasks in a package, if there is any failure in any task. It stores the fail task point in the check point file, once we restart the package the check point helps to start from last failure point specified in check point file.

        This feature is an added advantage for SSIS package which provides a better performance in order to achieve complex tasks.

Configuration of Check points

       SSIS stores the configuration information in an XML file whose name and location information. To implement check points in your package, you must follow the specific rules as follows.

CheckpointFileName: Specifies the full path and filename of your checkpoint file.

SaveCheckpoints: Specifies whether the package saves check points. [Set to TRUE , Default is FALSE]

CheckpointUsage: Specifies when to use checkpoints. 

àThe property supports the following three options:

o Never    : A checkpoint file is not used. (This is default)

o IfExists : A checkpoint file is used if one exists. This option is the one most commonly used if enabling checkpoints on a file.

o Always  : A checkpoint file must always be used. If a file doesn’t exist, the package fails.

To set the check points to the package

àRight click on anywhere in the Design area of the Control flow tab and select properties.

Example:

·  Take three Execute SQL Tasks are configure as

·  Select each Execute SQL Task à Properties àChange the FailPackageOnFailure property to TRUE.

·  Open the properties menu at the package level (Just open properties in the Control Flow without any task or connection manager selected)

Change the properties

Checkpoint Filename: c:\Checkpointexample1.xml

Checkpoint Usage: IfExists

SaveCheckpoints : True

·  Run the package and you will see the package is failed on the Second Execute SQL Task.

·  And checkpoint file is created at C:\

·  Correct the problem: Open the Execute SQL Task 2 and Configure the SQLStatement property: "Select 1".

·  The package has now completed and skipped the first step which already executed.

Note: Using checkpoints in the package can avoid repeating operations and save the time also. 

Let me know what you think about this article.