ghcoders dot net

As you may know, I’m not that much of a fan of Microsoft technology, but understand that it’s somewhat unavoidable and allowing for the use of MS products greatly opens the prospects of employment/work. So, at my new gig I’m aware that a healthy chunk of time spent doing development will be using Microsoft tools and all the quirks that go along with them. Sometimes I’m pleasantly surprised, sometimes I’m reminded of why I dislike Microsoft products in general. Lately it’s been more of the latter.

On the current client I’m designing stuff for, we’re using MS SQL Server 2005’s SSIS toolchain, which is a great idea with mediocre execution. For those who are unfamiliar with SSIS, it’s basically the evolution of their DTS package that came with SQL Server 2000, which is designed to simplify the ETL process of getting data from a source (or various sources) into the destination database after all sorts of cleansing and transformation. The DTS package that shipped with SQL Server 2000 was apparently pretty warty, a trait that is common amongst first-gen Microsoft products, and so SSIS is apparently notably cleaner and nicer. But, as most of us are aware, that doesn’t mean it’s wart-free, and I’ve found a nasty wart or two.

So, for the product I’m working on we have to load lots of files into the database, sometimes batches at a time. They’re segregated into different folders based on what kind of input files they are, and you need to be able to load all of the files in the folder that match a specific file pattern. Great, Microsoft provides a nice “control flow item” named the “Foreach Loop Container” which does exactly that. It looks just like a box with a little label on it where you can place other “control flow items”, and in my case I just use the “Data Flow Task” control item, because that’s what takes data from one place and transforms it and ships it to another. So, since Foreach Loop Containers are necessarily looping over files in a given folder (really it’s not as generic as the name sounds, it’s a “Foreach File in a Folder Container” really), you’d figure that there’d be a sensible way to hook up any file sources within it to, you know, the current file being processed. Well, there’s not. To get to it, you have to define a local variable for the container in the container definitions, and that will be re-set each time it starts a new file. That by itself wouldn’t be so bad if there was a straightforward way to make a data source that simply looked at that variable and was usable that way, but there’s not. In fact, to make it even worse, there is a specific “connection” type called the “Multiple File Connection”, which would work perfectly with this if it were designed to work cooperatively with the Foreach container, but no. The way Microsoft wants you to accomplish it is to set that local variable and then configure a flat file connection pointing to any random file (you have to point it to a file first to configure the output columns), and then tell it to ignore the actual file location you used in setup by overriding the ConnectionString property of the connection object. Of note is that if you should decide that you want to go back and change some of the output column formatting you have to once again go through the whole process of picking one of the files (even though it doesn’t actually use that file, it uses the ConnectionString property which we’ve overriden), then modifying it and then saving it. Luckily you don’t have to re-edit the property again.

All this discussion over what should be a pretty simple task. And another quick, but equally ugly wart is: in SSIS you can’t have a delimited input file that has a variable number of columns that will get parsed nicely for each line (and fill in blank/null outputs for the columns it fails to see). So if I have a CSV file with only 4 columns on the first line and 8 on the next, then my only option in SSIS is to set the number of columns to 4 and then write a script to process the lines that have separators still left in them.