So for two days I've been beating my head against the wall at work. The task was simple enough:

1. Gather some some data.
2. Cull the data.
3. Dump said data to a file.

Easy enough, right? Not so much.

The Beginning

I started this task like I would any other data centered task. I started with a script to get the data I needed. Easy-peasy. I had to gather data from several different tables and do some filtering based on data present in other tables. Nothing too complicated. I ended up with something along the lines of:

1. Select values that are in a valid state into a temp table.
2. Select values that have been the valid state at some point after a given date into the same temp table.
3. Filter out invalid rows in a first pass.
4. Filter out other invalid rows in a second pass.
5. Transform the temp table into useful form in a second temp table.
6. Get the return results from various tables based on the second temp table.

Didn't take long to crank out this script once I worked out all the requirements and figured out which buckets to dump the preliminary data into. So now on to the easy part. All I have to do is run my query in SSIS and dump it to a text file.

So easy.

If At First You Don't Succeed

So I'm new to SSIS (though I've learned more about it in the last two days than I'd have liked). I found the Data Flow control and thought I would be done by lunch. I thew in an OLE DB source and pasted in my magic query. Of course this angered SSIS. I was quickly told in no uncertain terms: F off.

Problem: Turns out you cannot use DECLARE or CREATE syntax inside of a sql command for an OLE DB source.

Maybe If I...

So I couldn't use my script directly. Fine. I'll make it a stored procedure and return my data set. I set up the procedure and we're in business. My somewhat complicated query runs on our test box in about two minutes. So I plug this into the OLE DB source via an EXEC in the SQL command. This proceeds to destroy Visual Studio. I give up on the process recovering after 15 minutes.

Problem: SSIS, in the interest of being easy makes you rely on magic to discover metadata about your queries to automagically populate column and transform information for you. Unfortunately, part of this magic involves adding a preamble to your query to pull metadata and rows as a preview. This can lead to some terrible query plans for the metadata retrieval call.

Surely This Will Work

The thought occurred to me that if SSIS was searching for metadata on the return results of the stored procedure, I'd bypass this magic by just turning the stored procedure into a function. After trouble compiling the TSQL as a function, I quickly realize that temp tables are not allowed. I have to use table variables due to the data integrity contracts for SQL Server user defined functions. No problem. Quick fix. Back to the OLE DB Source and plug in a query against my function, passing in the parameter in fnMyFunction( ? ) form. SSIS laughs in my face.

Problem: SSIS is perfectly capable of parsing your query, as long as you don't try to pass a parameter to a function. Oh, so your parser understands ( 'hard coded value' ) but not ( ? ). You're a dick, SSIS.

If You Want To Play Dirty, I'll Play Dirty

I was now on a mission. It was time to go rogue and break some rules. So I broke out of the data flow and into the control flow. I added a script and added a CREATE/DROP statement for a temporary table (read: not a temp table or variable) that was actually going into the schema. I then selected the returned result set from the stored procedure version of the script into this table. I then piped into the data flow and just ran a select all from this table. SSIS was perfectly happy with this since I turned off design time validation on the OLE DB source. I funneled this into my flat file, then piped from the data control to another script that dropped the table before finishing execution.

Apparently I'm Lucky

While in the middle of this giant mess, my boss, while laughing at me for having the bad fortune to get this hellacious bug, told me that I was actually lucky that I was getting to work with SSIS rather than DTS. He then launched into a list of all the ways SSIS was superior to DTS.

That's all fine and dandy, but if I'm looking at two cars, it doesn't matter if one car has a 400 horse power engine, leather interior, and jetpack wings if I can't start either one and get from point A to B.

Who knows. Maybe SSIS will grow on me when I have some simpler queries to run. You didn't make a good impression, though, SSIS. I've got my eyes on you.