Microsoft advocates checkpoints - proceed with caution [SSIS]

re: Microsoft advocates checkpoints - proceed with caution [SSIS]

Eric Wisdahl — Fri, 04 Jan 2013 14:08:26 GMT

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.

re: Microsoft advocates checkpoints - proceed with caution [SSIS]

andyleonard — Sat, 05 Jan 2013 01:53:37 GMT

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!

:{>

re: Microsoft advocates checkpoints - proceed with caution [SSIS]

Tim Mitchell — Sat, 05 Jan 2013 18:47:12 GMT

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.

re: Microsoft advocates checkpoints - proceed with caution [SSIS]

Aaron Lowe — Sun, 06 Jan 2013 18:12:18 GMT

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.

re: Microsoft advocates checkpoints - proceed with caution [SSIS]

Paul Williams — Mon, 07 Jan 2013 16:46:41 GMT

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.

re: Microsoft advocates checkpoints - proceed with caution [SSIS]

jbooker — Tue, 08 Jan 2013 16:07:23 GMT

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