Using dynamic SQL in an OLE DB source in SSIS 2012

Question

I have a stored proc as the SQL command text, which is getting passed a parameter that contains a table name. The proc then returns data from that table. I cannot call the table directly as the OLE DB source because some business logic needs to happen to the result set in the proc. In SQL 2008 this worked fine. In an upgraded 2012 package I get "The metadata could not be determined because ... contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set."

The problem is I cannot define the field names in the proc because the table name that gets passed as a parameter can be a different value and the resulting fields can be different every time. Anybody encounter this problem or have any ideas? I've tried all sorts of things with dynamic SQL using "dm_exec_describe_first_result_set", temp tables and CTEs that contains WITH RESULT SETS, but it doesn't work in SSIS 2012, same error. Context is a problem with a lot of the dynamic SQL approaches.

This is latest thing I tried, with no luck:

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM ' + @dataTableName

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr +',','') + [name] + ' ' + system_type_name FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1)

exec('exec(''SELECT * FROM myDataTable'') WITH RESULT SETS ((' + @listStr + '))')

Answer 1

So I ask out of kindness, by why on God's green earth are you using an SSIS Data Flow task to handle dynamic source data like this?

The reason you're running into trouble is because you're perverting every purpose of an SSIS Data flow task:

• to extract a known source with known metadata that can be statically typed and cached in design-time
• to run through a known process with straightforward (and ideally asynchronous) transformations
• to take that transformed data and load it into a known destination also with known metadata

It's fine to have parameterized data sources that bring back different data. But to have them bring back entirely different metadata each time with no congruity between the different sets is, frankly, ridiculous, and I'm not entirely sure I want to know how you handled all your column metadata in the working 2008 package.

This is why it wants you add a WITH RESULTS SET to the SSIS query - so it can generate some metadata. It doesn't do this at runtime - it can't! It has to have a known set of columns (because it aliases them all into compiled variables anyway) to work with. It expects the same columns every time it runs that Data Flow Task - the exact same columns, down to the names, the types, and the constraints.

Which leads to one (terrible, terrible) solution - just stick all the data into a temporary table with Column1, Column2 ... ColumnN and then use the same variable you're using as the table name parameter to conditionally branch your code and do whatever you want with the columns.

Another more sane solution would be to create a data flow task for each of your source tables, and use your parameter in a precedence constraint to just pick which data flow task should run.

For a solution this poorly tailored for an out-of-the-box ETL, you should also highly consider just rolling your own in C# or a script task instead of the Data Flow Task provided by SSIS.

In short, please don't do this. Think of the children (packages)!

Answer 2

I've used CozyRoc Dynamic DataFlow Plus to achieve this.

Using configuration tables to build the SQL Select statements, I have a single SSIS package that loads data from Oracle and Sybase (or any OLEDB source) to MS SQL. Some of the result sets are in the millions of rows and performance is excellent.

Instead of writing a new package every time a new table is needed, this can be configured in minutes and run on a the pre-tested and robust existing package.

Without it I would have been up for writing hundreds of packages.