Thursday, January 16, 2014

SSIS: 'to find this programmatically needs some circus'

In 2005 Microsoft introduced SSIS, which is a nice friendly GUI interface for defining dataflows between systems. It replaced DTS, which was also a GUI interface, albeit quite a limited one that was a pain to get to do simple things (secure copy instead of FTP? GTFO.)

Like a lot of friendly GUI things, it's nice for the first 20 minutes you use it until the cracks start to show. Still, when dealing with moving data between multiple servers, it's fast to develop something and it performs well. HOWEVER.

Something I have hated about it from day one is this: when a row errors out, you can redirect it. You will be provided with a cryptic error code (you can Google it, no worries), a brief description, and a 'Column ID'. Not the name, the ID.

Here's the fun part. That ID is buried deep in the GUI. click-click-click. If you are a programmer type who likes to be able to get your info quick-like via a search of a quick command line command, well, go fuck yourself.

The ID USED to be searchable if you switched from the pretty picture of your flow to the underlying XML. XML is never fun to look at, but if you can get vital info from it, it's like how a dog can get away with eating its own shit because it has a nice personality.

So now it's 2014. If my calculations are correct, 2005 was 9 years ago. Today, though, it still only shows the numeric ID, and YOU CAN'T GET IT IN THE XML.

Even Google is little help. Sure, you can do it, but you have to go through a ludicrous number of hoops to get a goddamn descriptive piece of text a human might like to read. As this guy puts it, 'to find this programmatically needs some circus'.

http://www.codeproject.com/Questions/440653/how-to-Get-ErrorColumn-Name-in-SSIS

Thanks, Microsoft. There's just no reason for this.