SSIS To Fail or not to Fail??

              The package referenced in this post can be downloaded here.  Please let me know of any issues or errors that you may encounter with either the package or the download.

               I recently posted about using precedence constraints in an SSIS package to control the flow of a package and provide limited error handling.  I received several question about this post, all of which focused on how precedence constraints can be configured to evaluate the completion of multiple tasks success or failure as a whole rather than individually.  Consider this example:

  1. Package1 contains three tasks
    1. Task 1 must always execute
    2. Task 2 must execute only if Task 1 succeeds
    3. Task 3 must always execute
    4. Task 1, 2, and 3 must execute in order
      1. Regardless of success or failure of the previous task

Now consider how we could use precedence constraints to accomplish this. 

  1. Task 1 is the first task so will always execute
  2. To insure that task 2 executes only when task 1 succeeds an on success precedence constraint must be used
  3. To insure that task 3 always executes on completion of task 2 completing an on completion precedence constraint must be used between task 2 and task 3.

              And here lies the problem!!  With these requirements if task 1 fails task 2 will never execute, as is required, but this also means that task 3 will never execute since task 2 cannot complete unless task 1 succeeds.  This would appear to be easily remedied by connecting task 1 to task 3 with an on completion precedence constraint, but this would violate the requirement that all tasks must execute in order.  The diagram below illustrates the specific requirements:


               This conundrum can be easily overcome by encapsulating tasks 1 and 2 in a sequence container and using an on completion precedence constraint from the sequence container to task 3. 


              Kewl right?!?!  Well let’s add an extra degree of difficulty.  Based on the requirements outlined if task 1 fails and task 2 never executes then the sequence container should NOT fail based solely on the failure of task 1, and should show as successfully executing.  Before you jump to judgment and declare this a RIDICULOUS requirement, as I did when I was assigned the project, consider that you have implemented logging to capture errors and the container failing would result in an erroneous error logged since both tasks are not required to succeed, or even execute, in order to show the sequence container completed successfully.  After a quick review of the properties of the tasks within the container you would immediately think that the FailParentOnFailure property could be used to insure that if a task in the container fails the container will still show success.


              WRONG AGAIN!!  This properties default value is always set to False and executing the package with either task 1 or task 2 failing demonstrates that the sequence container will also fail despite both tasks having this property set to False.  I REALLY began to question this behavior and had to Google…  I MEAN Bing it.  I ran across this post on Microsoft Connect which explained this behavior.  The Microsoft response answered my question, although it doesn’t seem incredibly intuitive to me:

 “Despite some pretty circular previous messages, we believe that the feature is behaving as designed. When you set FailParentOnFailure to false, the parent will not fail until the number of failures in the child exceeds the MaximumAllowedErrors threshold. When you set FailparentOnFailure to true, the parent will fail on the first occurence of an error regardless of the MaximiumAllowedErrors threshold.

              Hooyah!!  Don’t hate the player hate the game, now knowing the rules it is easy to meet the goals based on the requirements outlined for this package.  The FailParentOnFailure property is already properly configured, in this situation the default value of False, and now me must change the sequence containers MaximumErrorCount from the default value of 1 to 2.  The default value of this property is set to 1, meaning that if any task or container within the container encounters a single error then the task, or container, will fail.  By changing this value, in this case it can be set to 2, if task 1 or task 2 encounters an error the sequence container will still succeed since only a single error was encountered.


Executing the package shows that

  1. Task 1 fails
  2. Task 2 never executes, which is expected and desired since it should only execute on success of task 1
  3. The sequence container shows that it succeeded, despite task 1 failing
  4. Task 3 executes


             I am REALLY hoping that some of you have been to any of my SSIS classes or SQL Satirday Sessions on Dealing With Errors in SSIS 2012 and will ask “Wait a minute David?!?!?!  You told me that a single error can raise multiple error messages?!?!  What if a tasks raises multiple error messages?  Will this affect the MaximiumAllowedErrors value?”  GREAT QUESTION!!  and the answer is no.  Even though one single error may provide a collection of error messages, it is still only a single error so you WILL NOT need to provide an estimated number for the MaximiumAllowedErrors property.  If an error occurs, regardless of the number of error messages, it equates to a single error.

             I will be presenting Dealing With Errors in SSIS 2012 at SQL Saturday Costa Rica 2013 which will cover this topic so PLEASE attend and let me know of any questions that you might have.

Leave a Reply

Hit Counter provided by Sign Holders