An Ode to a Pre-Con– John Welch is Completely Awesome.

I am honored to be part of the team which is bringing SQL Saturday 89 — Atlanta, GA  to the community on September 17th. As an extra special treat, we are also bringing two Pre-Cons on the Friday before the Pre-Con for only $99.

Troubleshooting and Performance Tuning with Kevin Kline

AND…..

Data Warehousing with SSIS Deep Dive by MS SQL MVP —-John Welch

Today I want to tell you why you should attend John Welch’s Pre-Con.

At $99 –This is a bargain. Some phrases become trite with overuse. Phrases like: “A Fraction of the Cost”, “Gubernatorial”, “Buy now and get this personal pizza oven”.  But Google “SSIS training” and look at the prices.  Also look at the fact that this is only one day off of work, and if you are local, no travel. Also, you get John Welch live and in person. You can ask him questions!

John Welch (Blog | Twitter) is completely awesome.  He’s a skilled practitioner and a great speaker. He’s a Microsoft SQL Server MVP and was a contributing author to MVP Deep Dives. I learn something from John every time I see him–see this blog  post for how John helped me with Error Handling in SSIS. He’s been doing this ETL stuff for a long time and he’s got more tricks for doing it quickly and properly than Jason Bacani (another team member– Blog , Twitter) has shoes (next time you see Jason, ask him how many shoes he owns and report back, it’s astounding!). There have been many times when I have been completely stumped with a problem and found the answer on John’s blog  (he’s really good at working with XML in SSIS!).

Do you use BIDS Helper?  John Welch worked on that project. That’s right. John Welch helped write the code that turns your connection managers blue and pink and resets your GUIDs when they are trying to ruin your life ! Wouldn’t you like to learn from the man who helped tame SSIS with BIDS Helper and other similar projects ? Shouldn’t you thank him?!!!

Survival. If the subject matter in this session is not part of your job today, it will be soon. You will always need to know how to move data from Source A to Destination B. No matter the size your organization or your relationship with said organization’s data, there is almost always a moment when you are faced with importing or exporting data. It comes with being a DBA. While there are as many ways to accomplish this as there are DBA’s out there to do it; SSIS is actually built exclusively for this purpose. It may have bulky bells and whistles, but SSIS at its core is THE tool given to us by Microsoft to move data. Shouldn’t you learn how to bend it to your will? Could there be a better opportunity? So come on people: click the button!

http://www.eventbrite.com/event/1804919563

Thanks Daytona Beach SQL Server User Group!

The brand new PASS SQL Server User Group in Daytona Beach let me present at their very first meeting July 13th.  Thanks to Brook Ranne and Jim McAleer for inviting me.  I debuted a presentation on the changes forthcoming in SSIS with Denali.  The slides are available here.   If you are in the Daytona Beach area please check this group out!

I also just wanted to share some exciting news for me.  I submitted three sessions for this year’s PASS Summit.  When the schedule came out, none of them made the final selection.  However, this week I found out there still might be hope.  PASS has put together a Community Choice Ballot of 20 sessions for you the community to vote on, and one of mine (SQL Smackdown with Aaron Nelson) made the ballot!  There are five available slots we are competing for.  As I always say, I would love your vote, but regardless of whom you vote for, you should let your choice be known, as there are amazing sessions on the ballot!  Voting ends July 20th, so hurry! And thank you readers for your support this year, without you I’m sure I wouldn’t be on the ballot at all.

Check out the Community Choice Ballot here.

Where have we been? and, Debugging SSIS Variables Part 3 — The Watch Window

So like all bloggers, Audrey and I made a resolution to blog regularly and frequently back in November of 2010.  We did well for a stretch, but we’ve had many good things happen and the blog has taken a hit.  I went to the inaugural SQL Rally two weeks ago.  I met for the first time a lot of great community people– Karla Landrum, Michael Blizzard, Mike and Karen Rhodes, Bill Graziano, Rick Heiges, Kendra Little, Jeremiah Peschka, Karen Lopez, Timothy Mitchell, Bradley Balls, Tom LaRock, Allen Kinsel,  Mark Broadbent— I know I’m forgetting lots of names.  It was wonderful to meet folks from all over the country and the world who are excited to learn about SQL Server.  I also got to hang with lots of my buddies whom I only get to see at events like these.

Then it was on to Microsoft TechEd which was hosted in our home town of Atlanta, GA.  Audrey and I spent some time at the PASS booth, encouraging folks to attend their local user group meetings.

Then there’s the book.  See Jorge’s post here on the great news about the SQL Bible for 2011.  Audrey and I will probably not be sleeping much in the next few months as we work on the Wiley SQL 2011 Bible.  It’s crazy exciting!

So if we become ever so slightly less vocal here on the ol’ Datachix blog; remember, it’s not you , it’s us!  And now on to our regularly scheduled blog post:

Debugging SSIS Variables Part 3 — The Watch Window

Continue reading

The Vaguest Error Message I Ever Saw in Integration Services

There was this one time when I was working on a project and we were trying to load a datawarehouse.   I came in one morning to find a team member staring at a failed SSIS package with this error message at a Data Flow Task, specifically coming from the query in the ole db source:

[SRC_OLEDB ] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: “Microsoft SQL Server Native Client 10.0”  Hresult: 0x80004005  Description: “Internal Query Processor Error: The query processor encountered an unexpected error during execution.”.

Vague much?

Continue reading

#SQLSat 70 — It was great! Read this if you attended my SSIS session.

Ugh! I screwed up. I’m going to tell you guys about it and beg your forgiveness.

What I did: I gave out some bad info in my session on SSIS in Columbia, SC this past weekend.

What I said: I said that when an SSIS package is executed using a SQL Server Agent job, that the Service Account under which SSIS is running under requires all of the necessary permissions (file, etc). I said that the Service Account for the agent also needs these permissions.

What’s wrong with that: The account under which SSIS runs under is not a player in jobs executed by the Agent. Only the Service Account that the Agent uses matters. If giving that account the necessary permissions is just not an option, then you need to execute the Agent job under a proxy account. Here is an excellent post covering this topic.

How I got screwed up: I got confused with this a few years ago. I had a file permissions error with an Agent job executing an SSIS package and could have sworn that I resolved it by giving the SSIS service account the necessary permissions.

What must have happened: Looking back, the agent must have been running under the same service account that SSIS was running under.

So there it is, my mea culpa. If you saw my session and are reading this, I apologize. Please tell anyone who was with you about my error, so that we can nip this bad info train in the bud. And to the gentleman in the front who doubted me, thank you. Without your help, I would still be operating with false info in my brain. I wish I had gotten your contact info–email me if you read this 🙂

Other than that, it was a fantastic, wonderful event. This was the world premier of my presentation Up and Running with SSIS; what really made me happy was that several of the attendees approached me afterwards and said that they had really gotten some necessary info out of it.

Finally, a PowerShell script I can get excited about!

Aaron Nelson (Blog | Twitter) and I have a little game we like to play where we drink beer and talk smack about each other’s favorite platform. I’m (mostly) an SSIS developer.  He’s a (SQL Server) PowerShell guy.  He’s always calling SSIS bloated and inferior, while my position is that PowerShell, while handy for many things, is just not suited for ETL work beyond straight-copy-data-pushes.

But today I found a PowerShell feature that I and all of my hard core ETL developer compadres can really get behind:  the ability to script the creation of an Environment Variable.

Continue reading

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.

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:

If only I had something yellow to wear. 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! ) 

Julie to Present “Cool Tricks to Pull from your SSIS Hat” at SQL Saturday #62 in Tampa FL

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.

Debugging SSIS Variables Part Two–The Locals Window

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