Consider the following scenario for an SSIS package:
A Sequence Container contains two jobs for loading data from an Excel file:
- Job A: Attempts to load data from an Excel file following a specific file definition.
- Job B: In case JOB A fails, Job B will be executed instead.
Outside the container, a final task is executed to reset data.
The issue I have, however, is that if JOB A fails, even though JOB B takes over and manages to process the file correctly, the container is marked as a failure.
Although I can circumvent this issue with the use of some variables and "Precedent Constraint" (this can be seen in the second screenshot above), I wonder if there is a more elegant way, in which I simply ensure that the Sequence Container is not marked as failed is the first task of loading data fails.
Now, the first thing that comes to mind for me is the use of MaximumErrorCount property on the container. However I have other tasks within the container and if they fail it could mess with the logic.
Perhaps nesting yet another container for just JOBS A and B within the existing container would be the way forward?
But really (I'm a hopeless idealist) what I was hoping to find is some sort of property for JOB A that says if job A fails, don't mark the container as a failure in the simplest way possible. Thoughts?
Best Answer
Have you looked at the Propagate variable for the OnError Event Handler of the task within the sequence?
Check this link for info. Basically, you disable propagation of the OnError event so the ancestor tasks (or sequence in this case) don't receive the error.