Messing with Errors in SSIS, or does the Propagate “Property” really exist?

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.

 

and Cut!

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 property beast.

Here is a play by play:

  1. 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!)

Change me to False while you have the chance! Run!

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.

Propagate, caught!

**HALTAGE –this word is my invention, unlike Sasquatch, who is TOTALLY REAL AND NOT AN INVENTION.

*** John Welch is completely awesome.  Just sayin.

Published by

Julie Smith

One half of the Datachix.com. ETL person. This picture is the best of me ever taken. Don't be too sad when you meet me for real :)

8 thoughts on “Messing with Errors in SSIS, or does the Propagate “Property” really exist?”

  1. Hi Julie,

    Excellent post!

    In the past, I’ve used a script task on the OnError event handler to dynamically set the Propagate property. If I’m running inside a Framework, it’s a way to say “This error was expected and doesn’t need to bubble, but that error? It’s bad – send it up the stack.”

    You taught me about setting the MaximumErrorCount property to 0 for “Ignore Errors”. Until I saw your presentation at SQL Saturday in Columbia SC, I’d been setting the property to 999.

    :{>

  2. I can’t seem to get this to work — here’s my situation. I’m using a ForEachFile loop to import data from Excel files; some of the excel files are missing a particular column (“age”). The variable that tracks the file names (User::vFileName) is initially set to an excel file that has the “age” column in the excel — i.e, the excel data connection has meta data that contains “age” as one of the columns. Within the ForEachFile Loop is a sequence container that contains a DFT which reads the appropriate excel file dynamically set in the vFileName variable per the ForEachFile loop. I’ve set the Propogate “property” of the DFT to false, just as instructed in your blog. Unfortunately, when the package attempts to load an excel file that does not contain the “age” column, an error still rises from the DFT to fail the sequence container as well, unlike your screenshot in your blog. Perhaps the excel data connection failure overrides the propogate property setting somehow?

    What do you think?

Leave a Reply