SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jflaig
jflaig
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 26
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.
Venkataraman R
Venkataraman R
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 214
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
jflaig
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 26
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
weitzera
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1002 Visits: 629
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
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1629 Visits: 2775
can you build the full text in the variable and then try exec

Regards
Durai Nagarajan
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1629 Visits: 2775
have you used sp_executesql for executing dynamic queries?

Regards
Durai Nagarajan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search