FailureOnPackage and Checkpoints

A couple of weeks i was experimenting with FailureOnPackage setting to true and false in combination with checkpoints. I was trying to find out whether i have to put int on true and false and what effect was with checkpoints.

You'll know what checkpoints are but i little explanation for those who haven't use checkpoints before. SSIS can restart failed packages from thepoint of failure, instead of rerunning the whole package. If a package is configured to use checkpoint, information about the 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 that the package is run (MSDN).

Below you can see a checkpoint with FailureOnPackage = True. On the left an initial run and on right the rerun.

When the FaulureOnPackage = False you'll see the following happening (on the left the intial run on on the right the rerun)

So it's very omportant to set the FailureOnPackage to true.

So how does the checkpoint file look like.

<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{24981BF3-705B-4AE8-9371-802509214EE1}">

<DTS:Variables DTS:ContID="{24981BF3-705B-4AE8-9371-802509214EE1}" />
<DTS:Container DTS:ContID="{8C9FE8D0-F622-411B-8C7C-8ADBB9F4CFC5}" DTS:Result="0" DTS:PrecedenceMap="" />
</DTS:Checkpoint>

So this file is only saying: the package stopped at this location. Nothing more. Deleting the file will reset the package and the whole thing will run when execute the package

Greetz,
Hennie