Microsoft advocates checkpoints - proceed with caution [SSIS]

jamiet — Fri, 04 Jan 2013 13:28:15 GMT

In December 2012 Microsoft published a whitepaper entitled SSIS Operational and Tuning Guide which you can download by clicking here.

I have skimmed through the whitepaper and it appears to be a compelling read however a couple of sentences caught my eye that I want to draw attention to here. These sentences refer to the use SSIS checkpoint files:

Some of the most common challenges with SSIS packages are in how to handle unexpected failures during execution, and how to minimize the amount of time required to finish the execution of an ETL process when you must resume processing after a failure. For control flow tasks such as file system tasks, checkpoints can be used to resume execution without reprocessing work that has already been completed.
When designing packages, one of the largest concerns is designing so that in the event of failure, you do not lose all of the progress the package has made up to this point. For items in the control flow of a package, this is accomplished by the use of checkpoints.

In blog post Why I don't use SSIS checkpoint files from March 2011 I explain my experiences of SSIS checkpoint files and why I choose not to use them. In addition there are a number of Connect submissions that speak to the problems associated with SSIS checkpoints:

SSIS Checkpoint File Object Type issue with complex data types
Peculiarity using CheckPoint files and the OnPostExecute event handler
The values of Object variables do not get stored in checkpoint files
Checkpoints within containers
Make it easier to configure restartability

Full disclosure – two of those were submitted by myself

Microsoft have not (as far as I know) publicly acknowledged any issue with SSIS checkpoints however I am certain that internally the SSIS product team know the current implementation is inadequate for many scenarios. With this in mind I am disappointed that a Microsoft whitepaper openly advocates the use of them without at least acknowledging their deficiencies.

Designing for restartability is hard. I personally choose not to use checkpoints and instead implement my own custom restartability solution; I am not saying steer clear at all costs but I hope that if you choose to use them that you both understand the implications of doing so and test that they behave as you expect.

@Jamiet

Why I don't use SSIS checkpoint files

jamiet — Thu, 10 Mar 2011 16:27:00 GMT

In a recent discussion in regard to general ETL best practises the subject of checkpoint files as a means for package restartability came up and I stated that I was dead against using them. For anyone that may care, here is why:

Configuring them is distinctly unintuitive (that's a matter of opinion but if you follow the link I'll wager that you will agree)
they don't make any allowance for loop iterations
they cannot store variables of type Object
they are limited in ability. There are many scenarios where you may want to execute certain containers regardless of whether the package is started from a checkpoint file but the current usage model does not allow for this.
they are ignored by eventhandlers, which wouldn't be so bad if there were a way to toggle this behaviour
in certain scenarios they dont work properly

I'll expand on the last bullet point. I have encountered situations where the behaviour for tasks executing concurrently is unpredictable. That is, sometimes the completion of a task that executes concurrently with a failed/failing task will make it into the checkpoint file and sometimes it won't. This is near-impossible to reproduce but it does happen as my good friend John Welch will hopefully concur (if he is reading).

Is anyone out there making successful use of checkpoint files within SSIS? I would be interested in knowing about that if so.

@Jamiet

Search results matching tag 'checkpoint files'

Microsoft advocates checkpoints - proceed with caution [SSIS]

Why I don't use SSIS checkpoint files