THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Microsoft advocates checkpoints - proceed with caution [SSIS]

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

image

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:

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

Published Friday, January 04, 2013 2:28 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Eric Wisdahl said:

Completely agree.

I used to suggest using them all the time in my presentations.  I have since run into several problems and have steered clear for the past four or five years both personally and in presentations.  

I think checkpoints can be incredibly useful, particularly if a few of the problems were resolved.  However, even with that you would still have to design the package in such a way as to use them successfully.

January 4, 2013 9:08 AM
 

andyleonard said:

Good points, Jamie,

  I concur. It is very easy to present checkpoints as a simple solution to bypass previously successful steps in a data integration process. But they are *not* simple.

  Thanks for the reminder, Jamie!

:{>

January 4, 2013 8:53 PM
 

Tim Mitchell said:

Jamie, I share your skepticism around SSIS checkpoints.  While they are an excellent idea in theory, the implementation doesn't lend itself to ease of use or clarity.  I avoid SSIS checkpoints, relying instead on rolling my own restartability elements in my package frameworks.

Hopefully this whitepaper won't cause too much confusion among the masses.

January 5, 2013 1:47 PM
 

Aaron Lowe said:

During the SSIS SQLPASS Precon in 2011, Matt Masson simply said don't use checkpoints in SSIS.  Personally I've never had a good use case where SSIS was the best place to manage check points.

January 6, 2013 1:12 PM
 

Paul Williams said:

Agreed with all the above.

When I first started using SSIS 2005 several years ago, I investigated the use of checkpoints. However, I came across several confusing & undesirable consequences of using them. This is even more apparent when combined with Transactions. I have avoided them ever since.

As you have correctly pointed out, what is most disappointing is that MS have failed to address the issues raised in Connect & then advocated the use of them in a white paper.

January 7, 2013 11:46 AM
 

jbooker said:

I learned the hard way that checkpoints are a bust.  Should have read Jamies blog first.  For my purposes if only the Event Handler problem were fixed, I could use checkpoints.  Let's hope with all the new data in SSIS 2012 catalog, the team will use that to make a robust restart framework and drop CP files altogether.

On a side note, is anyone having issues with the changes to 2012 flat file connection manager?  Specifically those meant to 'fix' lack of support for embedded qualifiers.  

http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx

I'm not finding a much in forums or blogs.  All my packages with qualifiers are failing.  Fails when row delimiter changes, for example which 2008 handled fine. Since the new behaviour can't be toggled on or off (you'd think something named ' AlwaysCheckForRowDelimiters' might affect it, apparently not) Instead of a fix this should be listed as a 'breaking change'.  Hopefully I'm missing something, but I fear not and info seems scarce.

TIA, Josh

January 8, 2013 11:07 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement