Excel Perspective revisited

Last week, I was asked to do something really simple. Most of my team was out of town and I was on “hold down the fort” duty. So my boss’ boss comes over with an emergency that needs immediate attention. Seems there’s a report that produces a spreadsheet that lists users who have done something bad, but doesn’t add any contact information. It needs two more columns so that the users’ bosses can actually call and yell at them.

So far, so good. Two more columns to a spreadsheet sounds easy enough. It takes me a while to actually find out how the report is generated, but I do.

Problem1: the report is basically a DTS package – not SSIS or anything else supported in the last 30 years – but an old version of Microsoft DTS that hasn’t been supported since the Lincoln administration. I’ve no clue how to work with the thing or how to even open it.
I button hole one poor, unfortunate comrade who got stuck in Ohio with me and he is almost as clueless as I, but suggests that maybe we can convert the package to SSIS so that we can manipulated it. I spend the rest of the day trying.
After a few hours smashing my head on my desk as hard as I can, I realize that the issue I’m having is that

Problem 2: the DTS package itself is corrupt, so it won’t port. I pull the DTS package from production and not only find out that the development version is corrupt, but that the development version is wrong. The flows are almost entirely different, and it’s pretty obvious that the dev version is way out of synch.
Armed with the “correct” version, I end up deciding not to upgrade to SSIS, since… hey.. I can actually open this package.

Problem 3: The package isn’t launched from Sql Server. It’s launched from this in-house scheduling tool that someone wrote in the late 90’s (in Power Builder, I think, if that tells you anything). And it’s all kicked off by a batch file… yes… a .bat… a 300-line .bat file.
I figure out the data relationships and modify the queries in the DTS and push to our dev server only to find out that the thing won’t run. Not only that, but I’m not getting any error trapping from the actual DTS – that is, the bat file is swallowing them.
I spend another couple hours trying to get the batch file to correctly report the errors when I noticed

Problem4: The batch file on dev is out of synch with the one in production. I pull down the prod one, add some error handling and (*ding*) now it runs and I have a valid error message logged.

Problem 5: I spend another hour tracking down the error message only to realize that the DTS package modification I did, didn’t “take” – meaning there were 4 queries and 3 of them saved when I pressed “save”, but the other didn’t. There was, after all, a reason that Microsoft decommissioned DTS.

Problem 6: once I got all this resolved, I was so excited that everything actually worked that I … well.. didn’t noticed that I’d actually messed up the query. It’s partly because I’d misunderstood the data relationships. It’s partly because I didn’t account for one of the error conditions. But it’s mostly because I was so distracted by the stupid plumbing that I’d missed the fact that water was coming through the roof.

I guess part of my point is to rant. But mostly it’s to point out how simple things can drive you nuts. From my boss’ boss’ perspective, it’s just two more columns to a spreadsheet –what can be so hard about that? In fact, I thought the same thing when I looked at it.

Gets back to the “Excel Perspective” that I’ve blogged about before. If you can do it in excel, why does it take 4 months to do it on a server?
This is why.
--kevin