Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS Nugget: Ignore a checkpoint

Checkpoint files are very useful mechanisms for recovering failed packages and I have talked about them quite a bit before, however, they do have one or two limitations.

One thing that people typically want to do is always execute a particular task regardless of whether a checkpoint file exists or not. There may be a number of reasons for this - the most typical scenario is thus:

Data is staged from a source system and downstream some errors in that data cause another task to fail. Later when you fix the bad data in the source system and re-execute the package you don't want only the failed task to execute first - you want the task that fetches the data from source to execute again as well.

Up until now I didn't think that was possible but recently David Noor from the SSIS product team explained how to do it in this thread. Rather than repeat verbatim what David said I have instead recorded a video that demos exactly how you can configure this. The video is below but if for whatever reason you can't see it, go here to see it on Youtube instead.

 

I have attached the package that I built in the video. 

Comments are welcomed.

-Jamie

 

Published Friday, May 11, 2007 7:40 PM by jamie.thomson
Attachment(s): IgnoreCheckpointsDemo.zip

Comments

 

Paul W said:

Jamie,

Useful article as it related to something I have been working on recenty.

As another example, consider a sequence of tasks that insert data into tables from a source of flat files as part of a data transfer. Should any task fail, the container holding all these tasks is transacted as you would want all the data to be rolled back, so that the destination is not left in a state where only part of the data has been inserted.

On fixing the problem and restarting the package you would want the container to restart from the first task, not the task that failed.

Your solution works with a container whether transacted or not which is good.

However, in my test it also worked by setting the tasks to FailPackageOnFailure as well as FailParentOnFailure.

Another question ? Why does a FEL container need to be used. This solution doesn't work for a Sequence Container. It may well be that this is the feature that Microsoft intended, but I'm not sure.

June 18, 2007 3:04 PM
 

jamie.thomson said:

Hi Paul,

If you click through to the thread linked to above you'll see that Microsoft are also perplexed about why this works for FEL container and not Seq container and are currently looking into it.

Regarding FailPackageOnFailure/FailParentOnFailure. Interesting one. best thing I can say is that its hard to predict what will happen in all scenarios because of the very varied conurtations of setting all of these properties.

Is that a cop-out answer? I think so :)

-Jamie

June 18, 2007 4:32 PM
 

Paul W said:

Jamie,

Not a cop-out answer but an interesting one.

I have had so many problems relating to containers and transactions when used together that I have lost faith in SSIS's ability to handle these correctly (sad to admit but all my testing indicates in this area it is flakey at best).

Get this. I opened a call with MS regarding the use of the SC with checkpoints and transactions. MS response ? Well it was different to yours above. I was told it was a feature but the analyst appeared unconvincing.

Their workaround was to change the PC's between the tasks in the container to an OR constraint rather than AND ? I found nothing in BOL, or could think as to why that would work.

It didn't. However (and now this becomes really flakey). If you copy this SC to a new package it now works !!

I also found that whether this fix works also depends upon the error that causes the task to fail.

Try it and see for yourself. I'd be interested to see whether you observe the same behaviour as myself.

June 22, 2007 9:22 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems