SSIS : Configuring Package Checkpoints

Configuring Package Checkpoints

What is Checkpoint?

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.

Benefits of Using checkpoints in a package .

• For example, a package that downloads multiple large files by using an FTP task for each download can be restarted after the downloading of a single file fails and then download only that file.
• For example, a package that performs bulk inserts into dimension tables in a data warehouse using a separate Bulk Insert task for each dimension can be restarted if the insertion fails for one dimension table, and only that dimension will be reloaded.

Lets play with it..

Step 1: Create a new Integration Project, and add a new package to it.

Step 2: go to properties and click Control flow, and

Step 2.a: set Transaction Option property as ‘Supported’

Step 2.b: Set SaveCheckPoints property as ‘True’

Step2.c: Set CheckPointUsage property as ‘IfExists’

Step 2.d: Set CheckPointFileName with the file location for checkpoints file

Step 3: Add some tasks, as below, and configure

Step 7: click on each task and go to properties and set FailPakcageOnFailure as ‘True’. Do this for each task.

Step 8: Set ForceExecutionResult for DFT task as ‘Failure’ (so that it will intentionally fail). This property can be set for any task, for which you want your package to restart from after failure of the package

Step 9: Execute the package; it will fail at DFT task

Step 10: Set ForceExecutionResult property of DFT task again to ‘None’.

Step 11: Save and build the package, and then again execute, we can see, that it started from DFT task, where it failed earlier

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.