SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSIS Checkpoints with Tasks Running in Parallel


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:

First Run

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. 

Second Run

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.

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by Sumit Rastogi on 23 November 2016

Hi Kevin,

I am facing the same situation, I have few parallel task all do the etl of multi million to billion of records, I want to know whether converting those parallel task to sequential will hit the performance or not. Or, any other way we can resolve the problem.


Sumit Rastogi

Leave a Comment

Please register or log in to leave a comment.