Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jdf35
Starting Member
1 Post |
 Posted - 2013-10-29 : 12:36:19
|
| 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, same error.This is latest thing I tried, with no luck:DECLARE @sql VARCHAR(MAX)SET @sql = 'SELECT * FROM ' + @dataTableNameDECLARE @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 + '))') |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-29 : 13:59:34
|
| SSIS cant change metadata at runtime so even if your table cant be determined beforehand you need to make sure the resultset is the same for stored procedure if you're using it as the source for SSIS.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
Using dynamic SQL in an OLE DB source in SSIS 2012