Saturday, December 12, 2009

Executing a task on failure of other tasks in SSIS

I saw a query on SSIS MSDN forum regarding sending a mail when any 1 of the 2 specific task fails at control flow. The way Send Mail Task was attached to those 2 tasks, using Preednce Cnstraint for Failure, was looking absolutely fine but the Send Mail Task was not geting executed on failure of any one of the other two tasks. In fact there was a small mistake in the configuration of precdence constraint and thats why thought of putting it here.
I will try to explain the scenario using three simpe Script tasks. This is how they are connected to each other using precedence constraint.
ScriptA and ScriptB are connected to ScriptC using Precedence constraint for Value as Failure in Precedence Constraint Editor as shown here

Now open the Script editor for ScriptA and change the default code as
Dts.TaskResult = Dts.Results.Failure Now let's execute the package. We will see that ScriptA will turn RED but the ScriptC will not execute. Try to configure ScriptA for success and ScriptB for failure by changing the default code and re-execute the package. Again we will find ScriptC not getting executed. (Why??.. we configured the precedence constraint for Failure.) Okay, lets open the precedence constraint editor and select the radio button "Logical OR". The appearance of the constraint at control flow will change and looks like
Lets execute the package again. This time ScriptC will execute if any one of ScriptA and ScriptB are configured to fail. The key was to change the default selection from Logical AND to Logical OR. Logical AND means both the constraints shoud be true (means both the Taks, ScriptA and ScriptB should fail which is not possible becasue ScriptB will execute only when ScriptA completes successfully) while Logical OR means any one of the two constraint should be true.

Lets configure the constraint between ScriptA and ScriptB to "Completion" and configure both the tasks to fail and execute the package.Also, select Logical AND for the precedence constraint between ScriptA and ScriptC. This time ScriptC should be excuted. This is how the control flow looks after execting the package

1 comment:

  1. Excelent, you save me.

    Diseño de Paginas Web
    www.naranjaweb.com

    ReplyDelete