The default behavior of SSIS when an error occurs is for the error to “Propagate” or Bubble up in lay terms to the very top and to stop the package execution. For instance, if I have a dataflow and it attempts to insert a null value into a non-nullable column, the task will error out and the package will stop running.
But of course this behavior of package haltage** is not always desirable. For instance, if you are looping through a directory of xml files and using an xml task inside the loop to validate an xml file against a schema (xsd), a failure (meaning a file failed to validate) can be used as information and as means to direct another desired action. You could move the bad file with a file system task out of the directory and move on to the next file to validate. In this case you wouldn’t want the package to stop.
How do you avoid a package execution haltage? One way is to use the MaximumErrorCount Property available on most tasks and containers. Its default value is 1, meaning at the first error the package encounters, that exceeds the Maximum allowed and therefore it issues a STOP to the package. You can change it to 0 (zero) and that means “error all you want, I will never stop.” The problem with using MaximumErrorCount to try and control package haltage is that errors will bubble up, or propagate to the parent items (sequence containers, for each loops, etc all the way up to the package) and cause the package to stop anyway. The way I have previously dealt with unwanted bubbling was to isolate, as much as possible, the functions for which I would tolerate errors to one package and then set the maximum error count to 0 on all of the items—the child task, the containers it resided inside and the package itself.
Arrows indicate the 3 separate MaximumErrorCount properties which would have to be individually configured to 0 to avoid Package Haltage.
Then John Welch (blog | twitter) *** told me the secret—and now I will share it with you. There is a Legendary, Wily property (it’s actually not a property, but it should be) called Propagate–>On Error , and you can change its setting to False. Once this is done, the errors will not propagate or bubble up and you have a more elegant way to prevent unwanted Package Haltage.
The following is a dialog between Julie1 and Julie2 as we attempted to find this illusive
Here is a play by play:
- Select the task in the control flow for which an error should not halt the package.
2. Go to the Event Handler Tab.
3. Click the Blue text:
4. Watch as the gray screen turns to the familiar pale yellow of BIDS. (you don’t have to drag a single task onto the error handler!)
5. Go to the variables of your package. Click the gray button for system variables. ( I click the column header over the names of the variables at this point to sort them, so that I can finally find this BEAST).
6. Change the default value from True to False.
Ta Da. Now your package won’t halt if an error occurs at this task.
Your photograph of “Propagate” will make you millions from the tabloids and you can quit this data racket and follow your passion to become Lady Gaga’s chief groupie.
**HALTAGE –this word is my invention, unlike Sasquatch, who is TOTALLY REAL AND NOT AN INVENTION.
*** John Welch is completely awesome. Just sayin.