Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using dynamic SQL in an OLE DB source for SSIS in 2012 Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 3:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:02 AM
Points: 3, 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.
Post #1509130
Posted Monday, October 28, 2013 11:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:11 AM
Points: 132, Visits: 168
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
Post #1509179
Posted Tuesday, October 29, 2013 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:02 AM
Points: 3, 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 + '))')



Post #1509458
Posted Wednesday, October 30, 2013 2:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
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.'
Post #1509948
Posted Thursday, October 31, 2013 12:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
can you build the full text in the variable and then try exec

Regards
Durai Nagarajan
Post #1510033
Posted Thursday, October 31, 2013 12:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
have you used sp_executesql for executing dynamic queries?

Regards
Durai Nagarajan
Post #1510035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse