Checkpoints with containers in SSIS

In the previous post “Using checkpoints in an SSIS package“, we discussed how to configure an SSIS package in order to enable the checkpoints. Now, In this post “Checkpoints with containers in SSIS“, we are going learn how to configure a package to restart from the first task of the container in which it fails instead of the beginning of the package.

Most of the time, in an SSIS package, containers are used to group a set of tasks in a single unit of work and if the package gets failed, we need to restart the package from the beginning of the container instead of restarting it from the beginning of the package. Let’s create a dummy package and demonstrate it.

Using checkpoints with containers in an SSIS package – Demo

In this demonstration, we are going to use sequence container to group multiple tasks in a single unit of work. Let’s create a package with sequence container as below:

• Create a new integration services project in visual studio.
• Rename the “Package.dtsx” to “CheckpointsWithContainers.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.
• Now, add a sequence container to the control flow and rename it as “SQC – Set of tasks as one group”.
• Next, move “Script Task 2”, “Script Task 3”, “Script Task 4”, and “Script Task 5” into the Sequence container.
• Connect the sequence container with the success precedence constraint of the “Script Task 1”.
• In sequence container, connect each script task to their next script task with the success precedence constraint. The package should look like below:

• In the previous post, we discussed that in order to enable checkpoints in an SSIS package, we need to modify few package-level properties. To modify them, click anywhere in the package background and press F4 key to bring the package-level property window and set values for below three properties:
  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\CheckpointDemoWithContainer.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.

• As of now, we have configured the package for checkpoints. Now, click on sequence container “SQC – Set of tasks as one group” to bring the property window of the container and set “FailPackageOnFailure” property value to True.
• Next, to group all the tasks, which ate inside the container, into a single unit of work, we need to set the “TransactionOption” property value to Required.
• Now, click on “Script Task 2” and press F4 key to bring the property window for the task and set “FailParentOnFailure” property value to True. The “TransactionOption” property value for the “Script Task 2” is by default Supported which will force this task to be the part of the transaction started by the sequence container “SQC – Set of tasks as one group”. Repeat the same for the “Script Task 3”, “Script Task 4”, and “Script Task 5”.
• 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.
• Now, right-click on the package in solution explorer and execute the package. If the package fails with the error “MSDTC service is not running“, start the MSDTC service by following below steps:
  • Open Run window.
  • Type “services.msc” and hit the Enter key.
  • Locate “Distributed Transaction Coordinator” service and restart it.
• Execute the package, the task “Script Task 4” will fail 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\CheckpointDemoWithContainer.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 which looks as below:

• Now, reset the “ForceExecutionResult” property to its default “None” for Script Task 4 and re-execute the package, and re-execute the package. The package should restart from the “Script Task 2” (first task inside the container) even though it was failed during the execution of “Script Task 4”. The sequence container “TransactionOption” property value which is set to Required, forces the package to restart its execution from the first task of the sequence container.

Above, we can see that the package gets executed from the first task inside the container and not from the beginning of the package. I have attached the sample project which can be used for hands-on practices. Click here to download it.

Thanks for the reading and please share your input in the comment section of this post.