Creating Checkpoints in your SSIS package

Want to save your progress: add a checkpoint to your package.

Checkpoints are added onto your control flow to ensure that if your package fails after multiple tasks have succeeded, the progress you have made will not be lost. Say you are doing a Data load of a million rows so you use a ‘DataFlow’ task and then you need to archive a .csv file with a ‘File System’ task. Now if for some reason the file system task fails you are now going to need to run the whole package again including the expensive data flow task. By implementing a checkpoint on the file system task it will save all of the information prior to that task failing. So that when we run the package for the second time it will ignore the first task and begin on the second. 

*Sample package execution with checkpoints enabled

Now in order to implement this we are first going to have to change three setting in our package properties window.

1.       The ‘CheckPointFileName’ has to be populated. There are no naming conventions required here, so name it what you like.

2.       The Second property is the ‘CheckpointUsage’ property; change this to ‘IfExists’ this will allow the package to restart from a checkpoint if that checkpoint file was created.

3.       The ‘SaveCheckpoints’ property should be set to true. This simply says that if there is a failure a checkpoint file will be saved.

Now that your package properties are set, for each task that you wish to make a check point you have to navigate to that task’s properties menu and switch the ‘FailPackageOnFailure’ property to ‘TRUE’. By implementing this methodology you are decreasing your chances of unnecessarily reprocessing a task.

Follow the same methodology as above with a sequence container; it will have no effect on checkpoint performance. However, If you would like to process the sequence container as a checkpoint then (meaning if any task within the container fails then the package will restart from the beginning of the sequence container):

1.       On the task level property turn ‘FailPackageOnFailure’ to ‘False’ and Change the ‘FailParentOnFailure’ to ‘True’.

2.       Then in the sequence container properties change ‘FailPackageOnFailure’ to ‘True’.

3.       Then Set the sequence container to transacted by:

a.       Change container property ‘transaction option’ to required

b.      Change contained tasks’ ‘transaction option’ to supported

It is not possible to use checkpoints within the For Each Loop and the For Loop containers. This is because SSIS does not keep track of the number of iterations the loop has gone through during processing.