Is it possible to execute a container regardless of the checkpoint file?

I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?

Here's the scenario:

I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints. The package is setup to use checkpoints so that if a task fails the package will restart from that failed task TaskA is insignificant here. TaskB fetches some data and puts it in a raw file TaskC inserts that raw file data into a table.

Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.

So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.

How do I configure the package in order to execute TaskB again? Is it even possible?

Regards

-Jamie

How about controlling when the checkpoint files get created, and make sure you only do that when you don't have that kind of dependence, i.e. make the task creating the raw file NOT create a checkpoint after it finishes successfully?

/Kristian

|||

That sounds like the right sort of approach but I'm not sure if its possible.

Hoping someone from the dev team replies.

-Jamie

|||

Since the FailPackageOnFailure on the Task needs to be set for the failed task to result in a checkpoint file, I'd play with:

* Don't set FailPackageOnFailure on your last task that uses the raw file.

* Do set FailParentOnFailure on that task to have your higher level sequence container fail, and set FailPackageOnFailure on that sequence container, to have the sequence container generate a checkpoint file, which hopefully will restart from the beginning of the sequence container.

All totally untested, I didn't want to take the fun away ;-)

Cheers/Kristian

|||

I've got a vague recollection of trying to do this once using Sequence containers but couldn't get it to work. In theory it should, but I don't know how to.

Anyone? Anyone?

-Jamie

|||

Can anyone help wih this?

-Jamie

This request comes up more and more. I'm 99% sure it can't be done but wanted confirmation. If it can't be done, consider it a feature request.

|||

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

|||

David,

That is a very very good idea. Thank you.

And thank you for filing internally. Do you really think its a bug? I don't think it is. Currently I think it works "as designed" but I happen to think the design is wrong.

Regards

-Jamie

I previously marked David's response as an answer but I've temporarily unmarked it so that this [Microsoft follow-up] tag filters thru.

Please could someone address my question in this thread.

|||

Sorry Jamie, I must have missed the follow up question.

At least to myself and others on the team, it seems fairly inconsistent to restart a container without restarting its contained task, and the fact that the For loop container behaves differently than the Sequence container in this regard makes it clear that there is something to be addressed.

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

-David

|||

Excellent. Could you keep us updated?

Thanks David.

-Jamie

|||Sure thing, Jamie.|||

David Noor (Microsoft) wrote:

The team is discussing the issues and inconsistencies around checkpoints now to determine what needs to be corrected and how it should behave.

Hi David,

What was the outcome of this? Is the behaviour going to be changed?

-Jamie

|||

David Noor (Microsoft) wrote:

In the case where you have tasks B and C, with a precedence constraint from B to C, you can configure your package to restart at B after C fails the package by wrapping B and C in a For Loop Container that contains B->C and iterates over B->C just once. So in your example, you would change:

TaskA -> TaskB -> TaskC

to

TaskA -> For Loop Container[ TaskB->TaskC ]

Then, you'll need to set up your package so that the container executes just once, and the tasks inside the container don't fail the whole package, but instead just fail their container, thereby causing the checkpoint restart to start at the container. In this example, you'd define a variable "i", and make the following property changes:

For Loop Container: FailPackageOnFailure = true, InitExpression="@.i=0", EvalExpression="@.i<1", AssignExpression="@.i=@.i+1"

TaskB: FailPackageOnFailure=false, FailParentOnFailure=true

TaskC: FailPackageOnFailure=false, FailParentOnFailure=true

The For Loop is necessary only because checkpoint restart on a sequence container doesn't currently cause the sequence container's tasks to execute. I've filed an internal bug on this issue, but for now the For Loop approach should work well.

All,

I have done a video demoing David's workaround here: http://blogs.conchango.com/jamiethomson/archive/2007/05/11/SSIS-Nugget_3A00_--Ignore-a-checkpoint.aspx

-Jamie

|||Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David|||

David Noor (Microsoft) wrote:

Hi Jamie,

There are a number of checkpoint issues that we've collected, and work needs to be done to make them behave consistently in all cases, including this. I can't commit to a date/release, but I'd expect checkpoints to be made more consistent in a future release, and for these issues to be cleared up.

-David

ARRGHHH! That sounds like "not in Katmai" to me.

Thanks for the reply anyway David.

-Jamie

sql