Checkpointing in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

If you ever developed sophisticated DTS package, and there is another error in one of the task, usually you have to reexecute the package from beginning. For complex package and big data that get involved in ETL process, it will consume considerably amount of time wasted. In SSIS, you have option not to start package from beginning in case the package gets error. The feature in SSIS to address this issue is checkpoint.
Checkpoint will note execution information to a file, when the task in a package gets failure to execute. When you correct the task, and re-execute the package, the beginning of execution will start at the failed task not the first task of a package. The package will read execution in the file, and resume executing the task and the rest. Once it is successfully executed, the package will remove the file. Nice feature that help you a lot when you develop a sophisticated package.
To enable checkpointing of a package, you define package’s properties in Checkpoints section, you specify Checkpointfilename, set CheckPointUsage to IfExists or always, and set to true for SaveCheckpoint. Then you set FailPackageOnFailure property of a task to true, otherwise the package will not create checkpoint file if it fails.
To simulate the problem, create 3 execute SQL Task package, set second package property name ForceExecutionResult to failure, and run the package. If you notice the output window, package status will have failure status, then you will find checkpoint file that you specify on Checkpointfilename property. Let’s say you have corrected the task, for demonstration purpose, set ForceExecutionResult property of second task from Failure to success. Run the package again, this time you notice that package will be executed from the second task (failed task) rather than first task. Once it is successfully executed, the checkpoint file will no longer exist, because the package deletes the checkpoint file that no longer useful.
Checkpoint feature in SSIS is brand new feature that worth to be implemented, to make your SSIS package is more reliable and manageable.