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 + '))')

What if you tried SET FMTONLY OFF; EXEC MyProc in your OLEDB Source. Does that make it any better? — billinkc
– billinkc, Commented Oct 29, 2013 at 19:00
Bummer. Had that trick work for a friend dealing with temporary tables when they moved from 2008 to 2012. — billinkc
– billinkc, Commented Oct 29, 2013 at 19:37
Yes. I can get this to work if my stored proc calls a function which using a RETURN TABLE, but I have not yet figured out how to make the function behave dynamically, so perhaps the problem has just shifted into a new area. — jdf35
– jdf35, Commented Oct 29, 2013 at 20:45

Kyle Hale · Accepted Answer · 2013-10-29 21:13:59Z

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)!

That's fine in theory, but in practice if you have 230 SSIS packages that need to be upgraded to 2012 with hundreds of ole DB sources setup and working perfectly like this in 2008 then you're looking for a quick solution.

user3093281 · Accepted Answer · 2013-12-11 23:23:12Z

1

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.

answered Dec 11, 2013 at 23:23

user3093281

111 bronze badge

Collectives™ on Stack Overflow

Using dynamic SQL in an OLE DB source in SSIS 2012

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related