Several months ago I blogged about why Checkpoints are a great feature in SSIS to use. It seems with every feature there are potential problems you can run into and it is no different with Checkpoints. The major problem or what some people would call a feature is when Control Flow tasks are run in parallel checkpoints act a little erratically. The obvious solution for this is to not run tasks in parallel and instead have all your tasks kick off sequentially.
Here’s an example of the problem:
I run my package with a task I know will fail. The package has Checkpoints configured so when the package fails it creates an xml file with the fail point. I have these tasks running in parallel so my real goal is to have the set of tasks on the right continue even though one on the left failed but, Checkpoints prevent it from continuing.
I have not corrected the problem in my #2 task yet but when I rerun the package I get a little bit further on the right side to task B. Every time I run the package now it will appear like this until I correct the error in the #2 task. This is strange to me because from the first run to the second run I moved from task A to task B on the right, so why on a third run do I not now move to task C? Until I finally fix task #2 I’m stuck at task B on the right.
Third Run – With corrected #2 task
Now the #2 task has been corrected and everything completes.
So the lesson here is if you want to use Checkpoints in SSIS you should run the tasks sequentially instead of parallel because your package will not run like you would anticipate.