在SSIS 2012中的OLE DB源中使用动态SQL [英] Using dynamic SQL in an OLE DB source in SSIS 2012

问题描述

我有一个存储的proc作为SQL命令文本，正在传递一个包含表名的参数.然后，该proc从该表返回数据.我不能直接将表作为OLE DB源调用，因为proc中的结果集需要进行一些业务逻辑处理.在SQL 2008中，此方法运行良好.在升级的2012程序包中，我得到无法确定元数据，因为...包含动态SQL.请考虑使用WITH RESULT SETS子句显式描述结果集."

问题是我无法在proc中定义字段名，因为作为参数传递的表名可以是一个不同的值，并且每次生成的字段都可能是不同的.有人遇到这个问题或有什么想法吗?我已经尝试使用"dm_exec_describe_first_result_set"，包含WITH RESULT SETS的临时表和CTE使用动态SQL进行各种操作，但在SSIS 2012中不起作用，出现同样的错误.上下文是许多动态SQL方法的问题.

这是我尝试过的最新尝试，没有运气:

  DECLARE @sql VARCHAR(最大)SET @sql ='选择* FROM'+ @dataTableName声明@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'')具有结果集(('+ @listStr +'))') 

解决方案

所以我出于友善地问，为什么在上帝的绿色世界上，您使用SSIS数据流任务来处理这样的动态源数据?

您遇到麻烦的原因是因为您在颠覆SSIS数据流任务的所有目的:

• 提取具有已知元数据的已知源，这些元数据可以在设计时进行静态类型化和缓存
• 通过简单(理想情况下是异步)的转换来运行已知的过程
• 获取转换后的数据并将其加载到具有已知元数据的已知目的地

可以使用带参数的数据源来带回不同的数据，这是很好的.但是坦率地说，要让它们每次带回完全不同的元数据而在不同集合之间没有一致性时，这很可笑，而且我不确定我想知道您如何处理工作的2008软件包中的所有列元数据.>

这就是为什么要向SSIS查询添加WITH WITH RESULTS SET的原因-这样它可以生成一些元数据.它不会在运行时执行此操作-不会！它必须具有一组已知的列(因为无论如何它们都将它们都别名为已编译变量).每次运行该数据流任务时，它都希望使用相同的列-完全相同的列，包括名称，类型和约束.

这会导致一个(可怕的，糟糕的)解决方案-只需将所有数据粘贴到带有Column1，Column2 ... ColumnN的临时表中，然后使用与用作表名参数相同的变量有条件地分支您的代码，然后对列进行任何操作.

另一个更合理的解决方案是为每个源表创建一个数据流任务，并在优先约束中使用参数来选择应该运行的数据流任务.

对于不适合现成ETL的解决方案，您还应该高度考虑仅使用C#或脚本任务而不是SSIS提供的数据流任务"来滚动自己的解决方案.

简而言之，请不要这样做.想想孩子们(包装)！

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

解决方案

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

这篇关于在SSIS 2012中的OLE DB源中使用动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！