How to start a package execution from where it finished previously.

Problem: How to start a package execution from where it finished previously.

Solution: Restart Packages by Using Checkpoints

When a package is failed, it is always painful to start over the whole process of some small validation or connection failures. This can cause a lot of time and resource consumption sometimes depending on where the package failed to execute, for example if the package has few staging steps and then some transformations and in the last a logging process. If the package is failed on transformation task and to get there you need to start over the staging tasks again which can cause delay of work and stage, the same load twice.

Alternatively, to get over this Integration Services can restart failed packages from the point of failure, instead of rerunning the whole package. If a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package is run.

Using checkpoints in a package can provide the following benefits.

·         Avoid repeating the downloading and uploading of large files.

·         Avoid repeating the loading of large amounts of data.

·         Avoid repeating the aggregation of values.

If a package is configured to use checkpoints, Integration Services captures the restart point in the checkpoint file. The type of container that fails and the implementation of features such as transactions affect the restart point that is recorded in the checkpoint file. The current values of variables are also captured in the checkpoint file. However, the values of variables that have the Object data type are not saved in checkpoint files.

Example:

In this article, I am going to show we can use check point to start from where it has failed.

Step 1: go to the control flow of the package and right click for properties or hit ALT+ENTER

Step 2: From the properties select checkpoint file name by clicking on the ellipsis button next to it and save to a location, check point usage to if exists and Save checkpoints to true. please see the picture below

Step 3: Set the properties of the task and containers to set package to failure.please see the picture below

OUTPUT:

Package failed at execute sql task on first run.

 

Checkpoint Saved location

Re running the package again:

 executes only the last step where it failed previously.

As we can see from the package execution progress the package restarted from the checkpoint.