Monday, June 8, 2015

SQL Server Brain Basher of the Week #015 - SSIS Failing package

Just like last Brain Basher, this week one also based on Integration Services. Let's try to understand a very common misconception on package failures.

If one of the tasks is failed during the execution, does it stop the execution of entire package?

Assume that you have configured two Script Tasks as below and the first one is configured to ForceExecutionResult to Failure.

When this package is executed, Script Task 1 gets failed. Does the execution continue with Script Task 2 or not? Let's execute and see.

As you see, the second task is not getting executed. If a question "Why" is asked, a common answer is "failure of previous task stops executing the package".

Yes, execution does not continue but it is not exact reason. The reason for this (and for many cases) is Precedence Constraint set between these two. Since it is set as Success, execution does not continue with the second task, hence it stops executing the package. However, if the Precedence Constraint is set as Completion, it executes the second task too.

Execution of the entire package at a failure of a task will be stopped if FailPackageOnFailure property is set as true for the task. It forces to stop the execution of entire package regardless of the Precedence Constraint set.

No comments: