using dynamic SQL in an OLE DB source for SSIS in 2012

Question

using dynamic SQL in an OLE DB source for SSIS in 2012

jflaig

Old Hand

Points: 349
More actions
October 28, 2013 at 3:47 pm

#278527

I have a stored proc as the SQL command text which is getting passed a parameter, which 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" that contains WITH RESULT SETS, but it doesn't work in SSIS, same error.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Venkataraman R Say Hey Kid Points: 694 More actions · Answer 1

This post talks about this: http://stackoverflow.com/questions/14366913/strange-issue-in-ssis-with-with-results-set-returning-wrong-number-of-columns.

HTH,

Venkataraman

jflaig Old Hand Points: 349 More actions · Answer 2

Thanks, but that doesn't really help. Here's the latest query I tried, still spits out the same error in SSIS:

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

weitzera Hall of Fame Points: 3442 More actions · Answer 3

Hi!

I don't really know the aswer to your problem, but thought I'd pop in anyway.

How does the next step in the process handle the dynamic nature of the output? Is it dynamic sql all the way down?

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

durai nagarajan SSCoach Points: 15778 More actions · Answer 4

can you build the full text in the variable and then try exec

Regards
Durai Nagarajan

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

have you used sp_executesql for executing dynamic queries?

Regards
Durai Nagarajan