Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Archive for the ‘Business Intelligence’ Category

SSIS Package Properties – CheckPoints

with one comment

This post will explain the properties used by SSIS in to make checkpoints work when executing packages.  There may be times when a package executes and fails for some reason.  In order to circumvent re-doing all the tasks that completed successfully you can use Checkpoints to start the package from where it last failed.  This post covers checkpoint files and other checkpoint properties used to successfully restart a failed package from the point of failure in order to not duplicate efforts already completed during the last run of the package.

image

The reason for using checkpoints in packages is to allow the package to start at the point of failure instead of from the beginning.  For instance, if you have 2 dataflow tasks in a package that take an hour each to execute – it is not beneficial to start the package from the beginning if the first dataflow task completed successfully and the second dataflow returned an error.  Using a checkpoint file and other checkpoint properties allows the package to start at the second dataflow task where it failed previously and skip over the first long running dataflow (which was already executed successfully) – saving time, resources, and logic to rollback what has been already committed.

How Checkpoints Work

SSIS Checkpoints are comprised of a Checkpoint file, a usage property, save checkpoints property, and the FailPackageOnFailure property.  These properties all work together in order to define how packages are restarted if a failure occurs.  The checkpoint file captures the information of the package required to restart properly from the point of failure, the usage property tells the package if checkpoints are being used and when to use them, and the save checkpoints property indicates if the package actually saves checkpoints to the checkpoint file.  The FailPackageOnFailure property is set at the container level for those containers you wish to enable restarts on failure.

Information captured in a checkpoint file

If you enable checkpoints in your package by configuring various checkpoint properties, at runtime SSIS will capture the restart position for the package within the checkpoint file.  It will capture the container that fails, variable values at time of failure (except the object variable type), transactions, and configured values at runtime.

How to use Checkpoints

Checkpoints within a package are captured at the host level of the task that fails.  For example, if there is an Execute SQL Task within a ForEach Loop Task and the Execute SQL Task fails – the package will be restarted at the ForEach Loop instead of the Execute SQL Task.  Any work completed before the failure within the ForEach Loop will be run again when the package is restarted.  All efforts are taken to rollback all transactions within the container if a failure occurs during execution.  This does not pertain to non managed code outside of the package execution environment however – such as in the case of using an Execute SQL Task that calls a stored procedure.  If the stored procedure is committing transactions within the SQL engine – outside the scope of the package – these transactions will not be rolled back by the package checkpoint system – and any work completed in the stored procedure will be duplicated the next time the package restarts.

Another thing worth noting when using checkpoints within packages, is that configuration changes between the time the error in the package took place, and re-running the package will not be reflected at restart.  This is part of the checkpoint process.  The runtime configuration values are captured within the checkpoint file and re-loaded upon execution in order to keep continuity with the last unsuccessful execution.  Once the package completes successfully, the checkpoint file is deleted from the file system and configuration changes will be reflected in the package upon next execution.

Explaining the Checkpoint Properties

There are 4 properties used for implementing checkpoints within SSIS packages.

  1. CheckpointFileName
  2. CheckpointUsage
  3. SaveCheckpoints
  4. FailPackageOnFailure

CheckpointFileName

As indicated above, the CheckpointFileName property is used to specify the path and file name that will be used to capture the checkpoint information of the package at run time.  As stated earlier – this file will be deleted from the file system when the package executes successfully.  This file captures the unique identifier of the package as well, so if you change the unique identifier after a failure, the checkpoint system will not work because the GUID’s no longer match.  This is done so that a package cannot execute using the checkpoint file of a different package.  System and user defined variable values are captured in the checkpoint file (except for object type variables), as well configuration values.

CheckpointUsage

This property simply indicates whether or not checkpoints are used within the package.  The available values for this property are:

  • Never – If the value is set to Never then the package ignores any checkpoint files defined and restarting the package starts it from the beginning.
  • Always – this value indicates that the package always uses the checkpoint file defined and the package always starts from the point of failure – if a failure occurs.  With this option set, the package will fail if a checkpoint files does not exist.
  • IfExists – This option indicates that if there is a checkpoint file for the package – it will be used to restart from failures, otherwise, the package is run from the beginning regardless of failures.

SaveCheckpoints

This property indicates whether the package will save checkpoints when the package is executed.  This property must be set to True in order for the package to restart at point of failure.

FailPackageOnError

This property must be set to True for all containers in your packages – where you want to be able to restart the package from in case of error.   If you have not set any container’s FailPackageOnError property to True when the package executes – it will not recover from where it left off.

Please see BOL for more information on this subject.

This feature in SSIS has some known issues – so if you have any experience with it – good or bad – please feel free to leave a comment.

Advertisements

Written by Simon Worth

November 8, 2009 at 10:47 am