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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.