How time flies! It’s T-SQL Tuesday Wednesday and this month’s festivities are being hosted by Adam Mechanic (blog | twitter). The topic – “Crap Code: Outing Your Inner Suckage”. Nice.
I like to think that a good part of my job is helping coworkers fix their own crap code. But that doesn’t mean I’m not guilty of dropping a few bombs myself. (Granted, I had much more opportunity for that when I was a developer.) The hard part of this exercise is picking one instance. I mean, I could go with the code that didn’t work. Or I could go with the SSIS package that I originally wrote in DTS and used the wizard to convert. It all converted, technically, but I couldn’t migrate it from one environment to another without changing a bunch of connections or even adding linked servers to some of the code. I finally got around to rewriting that hot mess as a stored procedure that, believe it or not, runs in about 1/10th of the time.
But I think I’d rather talk about that damn Inventory program I’ve mentioned a couple of times already. Technically it’s not exactly T-SQL, but there was just so much suckage I really have to share. I wish I could show you how bad this thing was. First of all, it was SSIS. (I know!) Secondly, it was actually 3 different packages that all went and grabbed information from the same set of servers. On the same schedule. Why, you might ask, did I create 3 different packages? Was there, perhaps, a logical delineation between the 3? No. Not really.
You see, it started out as a single package that collected the job status info from all the servers, so that I could present it in a single report that was emailed to me daily. After a while, we determined there was a need for a central SQL Server inventory to track all our instances and the databases on them and that type of data. Now, please understand something. I’ve never taken any sort of class in SSIS or even read a book about it. What I “know” about SSIS is by Googling what I wanted to do and copying what someone else did. I didn’t actually understand what I was doing. So I didn’t exactly want to go fiddling around with my existing, working package. So I copied it. And I modified it to get the new information.
Then someone wanted to know all the logins that had sysadmin rights on all the instances. So I added that to the second package. Then they wanted linked server information. So I threw that in. Finally, they wanted to know all the objects that use linked servers. That one was pretty time intensive, so I made that a separate package.
But wait, it gets worse. I ask that you keep in mind that I created all this a LONG TIME AGO. Ahem.
- Some of the queries required elevated rights. Rather than figure out exactly what privileges I needed, I used the sa account to connect to each server. And because the sa account password varies, I had to hard code logic into the package for what password to use for which servers.
- I needed temp tables, but I couldn’t maintain the connection between different tasks in the package, so I had to create regular tables. I also didn’t know you could create regular user tables in tempdb. So I used the model database instead.
- I had no knowledge of error handling in SSIS, so I just had any failed task dump a record into a log table. This never worked correctly; I’d get the server name, but I could never get the actual error to log. I finally worked around the problem by setting the error_count threshold high enough that the job wouldn’t fail.
- I used sp_msforeachdb. A lot. (Come to think of it, maybe that should have been my topic today. Nah, this is much more cathartic!) I used a lot of cursors, too.
- I let this go on for YEARS.
Seriously, I didn’t want to mess with it at all. It felt like a house of cards. Push finally came to shove when one of the managers wanted to join back to this data, which meant it would have to move to Production. That’s when I decided it needed to move to 2008. And that’s also when I decided it needed to move out of SSIS.
I’ve since migrated it to Powershell. And, you know, it really didn’t take that long. I started exactly where I started all those years ago, with the job statuses, and once I got that working, adding the rest was easy. And omg is it fast! Sooo much faster than the SSIS packages. Granted, I left out the part about objects using linked servers. I have it written, but it’s going to be a separate, on-demand type thing.
Now, given that I know very little about Powershell, either, I’m sure some day I’ll look back at this new program and see it as a piece of crap, too. But for now, it’s all rainbows and unicorns.
P.S. I swear this is my last post bashing SSIS…. Mainly because I don’t have anything else running in SSIS. 🙂