Using checkpoints in an SSIS package 2


Checkpoints in SSIS

In this post “Using checkpoints in an SSIS package”, we are going to learn how to configure checkpoints in an SSIS package.

SQL Server integration services use checkpoints in order to avoid the re-execution of an SSIS package from the beginning in case the package fails during execution. Checkpoints can be extremely useful in case the task prior to the point of failure takes a long time to execute. For example, if we are dealing with an SSIS package which is loading a huge amount of data from an OLTP server (highly transactional) to a destination server and is taking a long time to accomplish the data loading operation, and if the package gets failed after the data loading operation (at any other task being executed later on), we would not like to restart the package from the beginning which will take a long time to reload the same data again. In such cases, we can use the checkpoints available in SSIS 2005 and later versions.

If an SSIS package is configured to use the checkpoints and it fails, an XML file is created and the successfully executed tasks are noted in that file along with the variables values and other useful informations. On the other hand, if the package completes successfully, the checkpoint file is deleted.

Using checkpoints in an SSIS package – Demo

In order to enable checkpoints in an SSIS package, we need to configure few package-level properties to help the SSIS in understanding the checkpoint file. To understand the checkpoints and their usage, let’s create a test package and follow the below steps:

  • Create a new integration services project in visual studio.
  • Rename the “Package.dtsx” file to “Checkpoints.dtsx”.
  • Add 5 script tasks in the package by dragging and dropping them to the package’s control flow view.
  • Rename the script tasks as “Script Task 1”, “Script Task 2”, “Script Task 3”, “Script Task 4”, and “Script Task 5” and order them. Connect these script tasks with their next script task with the success precedence constraint. The package should look like below:
Dummy Script Tasks

Dummy Script Tasks

  • Configure the below three package-level properties to enable the checkpoints for the package. Click anywhere in the package background and press F4 key to bring the package-level property window.
    1. CheckpointFileName – The full path with filename of the XML file which gets created in case the package fails. The default value for this property is blank which means no location. In this demo, we are using “E:\Test\CheckpointDemo.xml” file path as a value in this property.
    2. CheckpointUsage – This property specifies when the checkpoint file will be used by the package. There are three options available for this property:
      • Never – This is the default value for the package CheckpointUsage property and indicates that the package is not using checkpoints at all.
      • IfExists – This is the most commonly used value in the production for the CheckpointUsage property. If this option is selected, the package will use the checkpoint file if it is available at the specified location. Otherwise, the package will start from the beginning. We know that the checkpoint file gets created only in case the package gets failed and the checkpoint file gets deleted if the package executes successfully. That is why this option is safe to be used in the production environment as the package will not be failing in every execution. In this demo, we are using “IfExists” option in this property.
      • Always – This option restricts the package to look for a checkpoint file before it executes. If the checkpoint file is not available, the package gets failed. That is the reason this option should not be used in the production environment.
    3. SaveCheckpoints – The default value for this property is False and we must set it to True to enable the checkpoints in the package. If this property is not set to True, the checkpoint file is not created even if the package fails. In this demo, we are using “True” option in this property.
Package level properties

Package level properties

  • Now, select the “Script Task 2” and press F4 key to bring the property window for this task. In the property window, set “FailPackageOnFailure” property value to True. Repeat the same for “Script Task 3”, “Script Task 4”, and “Script Task 5”. Leave the “FailPackageOnFailure” property value to its default to False for the “Script Task 1” as this is the first task in the package and is the starting point for the package in both cases whether the package completes successfully or it fails.
  • Next, for testing purpose, select the “Script Task 4” and press the F4 key and in the property window, set “ForceExecutionResult” property to “Failure” to force this task to fail. This property is very handy for testing purpose and can be modified to fail the package forcefully.
  • Now, right-click on the package in solution explorer and execute the package. When we execute the package, the task “Script Task 4” gets failed and in turn the package also. Have a look on the package execution output below:
Package fails with checkpoint enabled

Package fails with checkpoint enabled

  • Let’s go to the specified file location at “E:\Test\CheckpointDemo.xml”. We can see that an XML file has been created by the SSIS package which contains all the details related to the package execution state. Now, this file will be used to identify the starting point for the package during next execution. This is how it looks like:
Checkpoint XML file

Checkpoint XML file

  • Now, reset the “ForceExecutionResult” property to its default “None” for Script Task 4 and re-execute the package, it will restart from the point of failure. In our case, this was Script Task 4. Below image shows that the package starts from the point of failure and executes there after.
Package executes from point of failure onward

Package executes from point of failure onward

Once, the package gets executed successfully, the checkpoint file gets deleted from the specified location. I have attached the sample project which can be used for hands-on practices. You can download it from here.

In the next post, we will be discussing the usage of checkpoints with containers.

Thanks for reading and please share your input.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a comment

Your email address will not be published. Required fields are marked *

*

2 thoughts on “Using checkpoints in an SSIS package

  • Mike

    Hi, If you select the IfExists for the checkpoint file, do you need to make one beforehand to save the package with? Just curious if you need a placeholder file to start out with or not for the first run.

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan Post author

      Hi Mike,
      We don’t need to create a checkpoint file as a placeholder while using the IfExists option. This option automatically starts the package from the point of failure in case a checkpoint file exists at the given location. Otherwise, the package will start from the beginning. Also, please keep in mind that the checkpoint file gets created only in case the package fails and it gets deleted if the package executes successfully.
      Thanks,
      Gopal Krishna Ranjan