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.
The voting is open this week for the BI Track for SQLRally. SQLRally, a new offering from PASS, is a two day conference which will be held May 11 -13 in Orlando, Florida. I think of it as a cross between the free SQLSaturday and the mega conference PASS Summit in Seattle. This is the inaugural year and the organizers have opened the selection of speakers to the prospective attendees.
My husband and I got married May 3 (I’m not telling the year). As we were rushing to the airport en route to our honeymoon, we realized that in the chaos of the wedding, we had almost forgotten a present for our mothers on the upcoming Mother’s Day. I somehow found the perfect card given the circumstances, and we mailed them just as we were leaving. Here is a rendition of the card we each sent our mothers that year:
The reason I’m thinking of that anecdote is that here it is on Tuesday, and it’s my turn for a blog post, and I’m in sort of a rush again. Not on my way to Europe this time, but anyway; I’m busy.
I wanted to do a write up of SQL Saturday #62 in Tampa which occurred two weeks ago, but I feel like the content is going to be as dense and informative as the above referenced greeting card.
Suffice it to say that I had a fantastic time. Organizers Pam Shaw and Jose Chinchilla are to be commended for the hospitality, the food, the speed pass idea. It was a great weekend.
It was warm in Florida. This cannot be overemphasized. I really enjoyed the Sun (Blog | Twitter) and the way its shininess let me cavort around mittenless. I hope to return to Tampa every time there is a SQLSaturday in the winter.
My presentation was well attended and well received. Lots of folks came, including many people who work with SSIS and are pretty famous for it. Fortunately for me their presences neither made me faint nor throw up, always a bonus.
I left with more friends and professional connections, and perhaps most importantly, the most awesome SQL Saturday presenter’s shirt EVAH:
Finally, I’d like to “roll credits” so to speak and give a shout out to all the great folks I got to spend time with that weekend. (Loving the networking tab on the SQL Saturday Page!). Can’t wait to see you all again.
Aaron Nelson @sqlvariant
Adam Jorgensen @adam_jorgensen
Andy Warren @sqlandy
Bobby Dimmick @kr4ster
Brad Schacht @bradleyschacht
Brian Knight @BrianKnight
Brian McDonald @briankmcdonald
David Taylor @dyfhid
Denny Cherry @mrdenny
Jorge Segarra @sqlchicken
Jose Chinchilla @sqljoe
John Welch @john_welch
Jason E Bacani @jebacanisqldude
Jack Corbett @unclebiguns
Geoff Hiten @sqlcraftsman
Gareth Swanepoel @garethswan
Ed Wilson @scriptingguys
Erika Bakse @baksedoesbi
Kevin Boles @thesqlguru
Mike Wells @SarasotaSQL
Pam Shaw @pamshaw
Troy Gallant @gratefuldba
Tim Radney @tradney
Stacia Misner @staciamisner
And lastly, SQLRally voting for the BI Track will be next week. Don’t forget to vote! (for me! )
My 2011 Technical Resolutions can best be summarized with a song….
I’m just kidding—I can’t summarize my goals with a song. I don’t write songs. But I am great at surfing the internet and lookie what I found at www.wordle.net. It was a handy dandy, feel free to distribute your creations, category cloud maker! So here is my 2011 Technical Resolution Cloud:
I am honored to be presenting at SQL Saturday #62 which will be held in (hopefully) warm Tampa, Florida on January 15, 2011. The title of my presentation is Cool Tricks to Pull from your SSIS Hat, and it covers the basics of SSIS variables and the Expression language. I will also be participating in the Women in Technology panel discussion.
This event, the first of 2011, will be bustling with fantastic speakers. I’m especially looking forward to the Powershell/SSIS Smackdown with Aaron Nelson (Blog|Twitter) and Mike Davis (Blog|Twitter).
I am also going to the PreCon which will be the Friday before the main event. This is a huge bargain and I didn’t want to miss the opportunity for an entire day of training on (SAN) storage and virtualization for DBA’s and BI for $99. I imagine I’ll be switching from room to room throughout the day. Sign up before tomorrow to join me at this price!
From Pam Shaw and Jose Chinchilla on the PreCon–>
We will also be hosting a Day of Data on 1/14/2011, the day before SQL Saturday #62 in Tampa at the Italian Club in the historic Ybor City district. We are offering 2 all day sessions from which to choose. For the DBAs we have Denny Cherry presenting Storage and Virtualization for the DBA. For the BI focus We have Stacia Misner presenting Business Intelligence End-to-End. The cost is only $99 per person thru 1/5/2011, after that the price goes to $109. This price includes coffee, juice and donuts, lunch, and course materials. Click here to register for Day of Data.
From the book of Bloggering, Chapter 59 Verse 11: Thou shalt humbly accepteth better solutions to thine posits as they appeareth in thine Comments Section. Yea verily, although thou may fearest change, thou shalt learneth new and better coding in this manner and be pleasing in the sight of thine employer.
I had a blog post several weeks ago about debugging the values of SSIS Variables. I shared the method I have always used, which was simply (actually maybe it’s not simple at all) adding a script task into the control flow and placing a message box in the control flow, displaying the value of the variable. Continue reading Debugging SSIS Variables Part Two–The Locals Window