Using checkpoints in an SSIS package

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:

• 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.

• 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:

• 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:

• 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.

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.