Problems with OnError Event Handling

I have an ETL setup, with is a series of SSIS packages that call each other

Scheduler               - Handles the processing of the entire ETL
       Layer               - Process a Layer of the ETL (for example Pre-Staging Layer)
           Wrapper      - Sequences through instances of a single table, from multiple sources
               Loader     - Contains the data flow and processing for the individual table.

Each of the packages, except the Loader, is within a Sequence Container.  Each package has an OnError on the package executable.  The idea is that if a problem happens with one source, I want to log the error, send an e-mail and break out of the individual loader package, but continue with the other instances (Wrapper),  other tables (Layer) and only after the layer is loader will the Scheduler decide to continue or not.

What is happening is that when an error occurs in the Loader (mostly comes back as a validation error because the source system is not online)  occurs,  the error handling doesn't happen as I would expect.  Looking at this in Debug, the Wrapper OnError process, and then the  Layer OnError processing occur (which I don't want), before the Loader OnError process occurs.  At the Loader level, for the Data Flow task I have set the FailPackageOnError to True and FailParentOnError to False, and even set the Disable EventHandlers to False for the Execution boxes of the Layer and Wrapper, and still this behavior persists.

The only way I get something like I want is by physically deleting or disabling the OnError's in the Layer and Loader, but the end result isn't what I expect, and the system fails before moving on to the next table with a MAXIMUM ERROR COUNT message.

I am missing something basic in the behavior, and would appreciate assistance in explaining what I am missing.  Should I be using specific OnError Event handlers at the Data Flow, Sequence, and Execution blocks?  What should the Properties be set at each level?



Who is Participating?
HoggZillaConnect With a Mentor Commented:
I can see how you are stuck in a waterfall. If you are using package level On-Error, then any error downstream will roll up and trigger parent On-Error events. I believe you are going to have to add additional logic to your On-Error events to filter out the unwanted events and also set your packages NOT to fail on error.
Good Luck
All Courses

From novice to tech pro — start learning today.