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?