The Case of the Mysterious Failing Packages

Or, that time I pimp-slapped some GUIDs…

Let me set the stage for you. We’re less than a week out from a major production push. We move the SSIS (2008) packages over to the integration testing server to make sure everything is good to go. On each data load, something fails. Never in the same package, never in the same task, never from the same source data. We think it’s the server. We move the packages again. More failures.

I’m losing sleep, breaking out in a weird rash that may or may not be related to my panic, and my boss is at my cube every hour asking how things are going in his, “I’m trying to be supportive, but fix the damned thing already”, voice. I’m rethinking my Don’t Drink During Work policy. This is not good. I just can’t find anything wrong. I can run the packages independently and they’re all happy green boxes. I can run the whole load from the beginning and it’ll get past the point of the prior failure only to crap its pants further down the line. Now, full disclosure: I sort of suck at SSIS. It’s fairly new for me, and while I’m learning, I still have a long way to go. Let’s just say that my self-confidence was not at an, “I’m Okay, You’re Okay”, sort of level.

After the latest failure and an inappropriate-for-the-workplace, profanity-laden outburst from me where I may or may not have referred to SSIS as “the stinking offspring of a poxy two-dollar…”, oh, never mind… I hear a question from the next cube over. “Have you reset the GUIDs?” What? GUIDs? Uh, no. I stand up, plant myself in my co-worker Tom’s cube, and ask for an explanation. He explains that if packages are copied, sometimes the GUIDs can be the same in two packages. Then, if they end up running in parallel, it can screw up memory management and SSIS freaks out. And as we all know, when SSIS ain’t happy, ain’t nobody happy. We use a stock template for all of our packages that is copied and then saved off as the new package. And, since our dimension loads are pretty standard, if we need to add a new dimension, sometimes it’s easier to copy one load package and modify it rather than building one from scratch. Yes, I’ve definitely done some package copying in my short tenure as an SSIS developer.

Jeff wanders over, listens to my plight and confirms that I should definitely try resetting the GUIDs. These two guys, who have since been inducted into my Personal Heroes Hall of Fame, explain that with BIDS Helper (totally free download), I can reset the GUIDs on a package and hit every object within that package. A quick rundown of the feature and why it is useful is here. Luckily, I had already installed it for a couple of other features it offered, so I got to work. Can’t hurt, right? Our ETL process is fairly complex, and we have a lot of data moving around. Dimension loads, fact loads, auditing, staging, the works. It took about an hour between me and another co-worker, Tim, to step through all the packages, check them out of TFS (Team Foundation Server), reset the GUIDs, and check them back in. It was easy. Just right-click on the package, look down to the bottom of the menu, click Reset GUIDs, confirm, and you’re done.

We move the packages with their new and improved GUIDs back over to the integration testing server, and viola!, data’s flying around like Your Mama insults at a middle school. No failures. Let me reiterate: With no other changes, the sporadic failures stopped when we reset all the GUIDs.

Now, I can’t say with 100% certainty that resetting the GUIDs definitely fixed the problem. All I know is that we did it, and the failures stopped. That’s one hell of a coincidence if it wasn’t the issue. This is right up there with the legendary, “Try rebooting the server”, and his lesser known cousin, “Restart the service”. It is something to try when all else fails.

What did I learn? A few things:
1) SSIS does not have a personal vendetta against me. There was no ghost in the machine.
2) BIDS Helper is my friend. Just install it. You’ll love what it gives you.
3) If I copy a package, I make it part of my routine to reset the GUIDs.
4) When in doubt, curse loudly until someone comes up with an idea (just kidding). Seriously, this is a great example of why we should get up from our desks and enlist the ideas and knowledge of those around us. Turns out that I work with some pretty smart guys.

I’m Julie. I’m a Datachix.

The Reluctant Production DBA—or I’m a Big Girl Now.

I’m a Big Girl now.  Not in the Christina-Hendricks- from- Mad- Men sort of way (well maybe in that way too, but I digress), but in the Not –afraid- of- Servers- Networking- and –Maintenance-Tasks sort of way.

You see, I’ve been doing ETLS and programming for databases for ten years now, but I’ve always been much more comfortable inside the query tools rather than outside of them.  Outside is the scary realm of consoles, networking, users and permissions.  I didn’t completely avoid them.  I knew enough to do my job, but I must admit I was EXTREMELY grateful that the disks, logs, backups and maintenance tasks were not my responsibility.  Although this made me happy in the sense of “avoiding that which makes us uncomfortable” it did leave me with a bit of a Pinocchio complex.   I was convinced, and rightly so, that I would never flourish as a DBA until I sucked up my fears and did some learning.

So five months ago I took a leap into a job where I would be the first and only DBA.   Google, SQLServerCentral, and MSDN all became my lifelines as I forced myself through the transformation from  a “Development DBA”  to a “Development DBA Who Doesn’t Suck at Production Work Either”.

And I’ve learned some awesome stuff.  Installations?  Bring it.  Disk Configurations? I’m not scared.  Maintenance and Backups?  Sure.  Reindexing and Updating Statistics?  Whatever it takes.  Transaction Logs?  They’re my bitches.  SANs?  I’ll never be without one again.  Virtualizing a SQL Server?  I’ll get back to you.   Niedermeyer?  He’s a dead man.  (sorry that last one is not really relevant, but I was rolling).

So here we go.  I’ve got more to blog about now than I ever did.  I’m a big girl now.  Where’s my lollipop?

Why not a Manhattan Project for Data?

So, driving in to work the other day, I hear a discussion of the Christmas Day attempted terrorist bombing on NPR.  The guy being interviewed begins to describe the problems with data integration at the federal government level.  He talks about TWENTY-EIGHT different networks that had to be accessed in order to comprehensively review security information not so long ago.  Things are better now, but as a former federal employee, I can assure you that it probably isn’t better by much. 

Here’s my point:  Back during WWII, the government brought together the greatest scientific minds of the time to work on the Manhattan Project.  The end result was the atomic bomb.  Now, imagine if they did the same thing for their data today?  I say, bring together the rock stars of our industry, throw those 28 networks/databases on the whiteboard, and let them have at it.  That project would do more for our national security than just about any other investment we could make. 

And the coolest thing?  Maybe they give it a code name that is pure awesome.  Maybe someday, they make a movie about it.  Imagine Brad Pitt, in his role as Itzik Ben-Gan, debating architecture with Diane Lane, in her Oscar-worthy portrayal of Kimberly Tripp… tell me you wouldn’t pay $8 to see that.  And okay, fine.  Maybe I have this weird fantasy of being called in to some nondescript government office in the middle of the night and being asked to join this yet-to-be named project.  And maybe I hope that I’ll get some cool CIA code name.  And maybe, just maybe, I’m hoping for Jennifer Connelly to play me.

Hi, I’m Audrey. I’m a Datachix.

My goal with my portion of this blog is not to do long-winded case studies. I’m not going to go make up some artificial scenario so I can demonstrate something I’m not using. I’ve been in this business for a long time, and I’m always running into things that I probably should have known before now. That’s what I’ll share. All the dirty little secrets and things we’re ashamed to admit that we had to Google. You all know what I’m talking about.

Things you ought to know about me:

Given the choice, I like CAST over CONVERT. CONVERT sounds so, well, permanent. And if pressed, I’ll admit that CAST just sounds cooler. Like I’m performing some sort of datatype magic. I pronounce varchar so that it rhymes with “hair care”. But I always hesitate before saying varchar out loud, because there are so many variations on pronunciation that I worry about sounding weird. I use LEFT joins. RIGHT joins are for pretentious people. So is the word tuple. Quit showing off. And by the way, I don’t say “datum”. It’s “data” all the time. I know it’s not grammatically correct, but I don’t care. I hate COALESCE. I’ll use it, but tentatively. I like simplicity. I want people to look at my work and find themselves nodding. If you have to explain it, then it ain’t art, sugar. I indent. I capitalize. I document. I can’t remember what I had for breakfast yesterday; I sure as hell can’t remember why I did that weird thing with the data last year. I believe in table aliases that mean something. If the table name is ProductOrder, then my alias will be “po” damn it. Entities are singular. Underscores are evil. Back to aliases… I’m so anal that I’ll catch myself aliasing a table when I’m not even joining to anything else. If someone put a gun to my head, I don’t think I could write a cursor correctly. They don’t make sense to me. I think in sets, and if forced, I loop. I love Common Table Expressions. They’re just pretty on the page. WITH is a great keyword. I assume that the requirements are going to suck or not exist. That way, I’m never disappointed. I still search Books Online for most syntax. PIVOT intimidates me. Normalization is beautiful. Selective denormalization can be beautiful too. I want to know enough about my subject matter/industry/business to qualify as a junior business analyst. And I believe in long, slow, deep, soft, wet kisses that last three days. (I couldn’t resist)