Simple SSIS Configurations

SSIS Configurations just don't do it for me. I suppose that describes a lot of things in Integration Services from my perspective. Hey - I love the architecture. It seems like they spent a lot of time working that out. They just didn't spend a lot on the component bits - and probably because they figured "users" (developers) would be able to code their way around any limitations they found. Just like me. However, I don't consider myself a "user". I'm a developer, and the other person at my company whom I would have liked to get into SSIS would NEVER have been able to code around these issues, they would have had to create monstrosities of complex packages to deal with them.

Configurations enters that description. Overall, they're fantastic! The tiny problem I have (and many others) is that it's just slightly too restrictive for a very flexible tool. Enough so that it's extremely difficult to have a dev and/or test and/or prod system on the same server, with very few switches to flip to change the package behaviour. Sure, you could have two configuration databases, and identify the connection string on the DTSEXEC command line - but IMO, that's messy.

My solution? Well, if every problem looks like a nail, bring out the hammer! Custom component time - this time a Control Flow Task. Turns out to be a little simpler that a Data Flow Task - much simpler than my SCD task.

I created a little component that requires a connection (ADO only, of course - aha! A new gripe to blog about!) a table name, and a configuration filter (a static text value, or string variable). It reads all the rows of the table that fit the filter. It populates any variable named the same as in the table with whatever value is in the table. Yes - a lot more simple than SSIS's configurations - it only updates variables. But I can't think of a configurable element that I have that isn't tied to a variable. Yes, SSIS's configurations allow for really configurable packages - but I honestly can't see a need to read a Sort Data Flow component's "description" from a configuration file, can you?

So, how did that solve my problem? I can have one connection to the database the configuration resides in, regardless of "purpose" (dev/test/prod). I can then have my component read that single table (again, only one required - use more if you wish) with a dynamic variable-based filter. The variable I could use could be the System::MachineName variable (if I can identify those dev/test/prod environments by server name), by the System::UserName (if environments are identifiable by who's running the package), or by a user variable that gets populated however you like (expressions, scripts, ...).