Integration Services Checkpoints to restart package from failure

By: Ray Barley | Read Comments (3) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem
We have a number of SSIS packages that routinely fail for various reasons such as a particular file is not found, an external FTP server is unavailable, etc.  In most cases these error conditions are just a temporary situation and we can simply rerun the package at a later time and it will be successful.  The issue, however, is that we do not want to rerun the tasks in the package that have have already completed successfully.  Is there a way that we can restart an SSIS package at the point of failure and skip any tasks that were successfully completed in the previous execution of the package?

Solution
SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.  The Checkpoint implementation writes pertinent information to an XML file (i.e. the Checkpoint file) while the package is executing to record tasks that are completed successfully and the values of package variables so that the package's "state" can be restored to what it was when the package failed.  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 found.  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. 

The starting point for implementing Checkpoints in a package is with the SSIS package properties.  You will find these properties in the Properties window under the Checkpoints heading:

• CheckpointFileName - Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks.  Rather than using a hard-coded path as shown above, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.
• CheckpointUsage - Determines if/how checkpoints are used.  Choose from these options:  Never (default), IfExists, or Always.  Never indicates that you are not using Checkpoints.  IfExists is the typical setting and implements the restart at the point of failure behavior.  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 - Choose from these options: True or False (default).  You must select True to implement the Checkpoint behavior. 

After setting the Checkpoint SSIS package properties, you need to set these properties under the Execution heading at the individual task level:

• FailPackageOnFailure - Choose from these options: True or False (default).  True indicates that the SSIS package fails if this task fails; this implements the restart at the point of failure behavior when the SSIS package property SaveCheckpoints is True and CheckpointFileUsage is IfExists. 
• FailParentOnFailure - Choose from these options: True or False (default).  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.

Keep in mind that both the SSIS package Checkpoint properties and the individual task properties need to be set appropriately (as described above) in order to implement the restart at the point of failure behavior.

Before wrapping up the discussion on Checkpoints, let's differentiate the restart from the point of failure behavior with that of a database transaction.  The typical behavior in a database transaction where we have multiple T-SQL commands is that either they all succeed or none of them succeed (i.e. on failure any previous commands are rolled back).  The Checkpoint behavior, essentially, is that each command (i.e. task in the SSIS package) is committed upon completion.  If a failure occurs the previous commands are not rolled back since they have already been committed upon completion.  

Let's wrap up this discussion with a simple example to demonstrate the restart at the point of failure behavior of Checkpoints.  We have an SSIS package with Checkpoint processing setup to restart at the point of failure as described above.  The package has two Execute SQL tasks where the first will succeed and the second will fail.  We will see the following output when running the package in BIDS:

Task 1 is green; it executed successfully.  Task 2 is red; it failed.  If we run the package a second time we will see the following output:

Notice that Task 1 is neither green nor red; in fact it was not executed.  The package began execution with Task 2; Task 1 was skipped because it ran successfully the last time the package was run.  The first run ended when Task 2 failed.  The second run demonstrates the restart at the point of failure behavior.

Caveats:

• SSIS does not persist the value of Object variables in the Checkpoint file.
• When you are running an SSIS package that uses Checkpoints, remember that when you rerun the package after a failure, the values of package variables will be restored to what they were when the package failed.  If you make any changes to package configuration values the package will not pickup these changes in a restart after failure.  Where the failure is caused by an erroneous package configuration value, correct the value and remove the Checkpoint file before you rerun the package.
• For a Data Flow task you set the FailPackageOnFailure or FailParentOnFailure properties to True as discussed above.  However, there is no restart capability for the tasks inside of the Data Flow; in other words you can restart the package at the Data Flow task but you cannot restart within the Data Flow task. 

Next Steps

• Keep the Checkpoint capability in mind and implement it in your packages where appropriate. 
• When using Checkpoints make sure that all of the appropriate properties are set as described above.  You really have to include failures in your testing to make sure that you have Checkpoints setup correctly.
• Stay tuned for an upcoming tip on developing SSIS master packages that execute a number of child packages in a particular order.  The Checkpoint restart at the point of failure behavior will be implemented in the master package.
• Download a copy of the Checkpoint sample SSIS package here to experiment with Checkpoints.  Note that the package assumes you have a SQL Server instance running locally and the CheckpointFileName is hard-coded to c:\mssqltips\Checkpoint.xml.  Change these as appropriate for your environment.

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code