Get Well Soon Trevor, love the Datachix!
http://bit.ly/gzKzRX
{February 8, 2011}
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:
- 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.
{January 25, 2011}
I went to SQL Saturday in Tampa!
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:
Not a polo.
———————————————————-
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.
Name/Twitterhandle:
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
Eric Wisdahl @old_man_fish @EricWisdahl
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! )
{January 11, 2011}
T-SQL Tuesday #14—Julie’s 2011 Goals in “the Cloud”
It’s T-SQL Tuesday and the topic is Technical Resolutions for 2011. Thanks to MidnightDBA (Blog|Twitter) for hosting this month’s event.
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:
{December 7, 2010}
FOR XML a la Mode –a study on Modes, especially using For XML Path Mode to Concatenate Data
Hello folks, Julie here with the as promised weekly Datachix blog—
Hup Hup—did you say just say ”Julie” here?
Yes I did.
You specifically said last week that you and Audrey would blog weekly and ALTERNATELY. You posted last week. Isn’t it Audrey’s turn?
Yes, but Audrey is fiercely battling Reporting Services at a new client site–she can’t post this week. Also, I think there were Ninjas…. and maybe some Bear-Sharks……… she’s really really….. busy.
Fine.
Hello folks, Julie here with the as promised weekly Datachix blog. 
Recently a buddy of mine left a comment for us which went like this:
Can you post an example of putting data from multiple rows into a single column?
For example you have this:
Cat | meow
Cat | eat
Dog | bark
Dog | fetch
And you want to display it as this:
Cat | meow, eat
Dog | bark, fetch
It’s hard to find a good example of this online.
Thanks, Ben
