SSIS：有没有办法动态生成一些SQL任务？ [英] SSIS: is there a way to generate dynamically some SQL task?

问题描述

亲爱的所有人，



我是SSIS的初学者。

我有一个表名列表，我必须这样做创建特定的SQL任务。每个SQL任务都必须运行相同的存储过程，它接受表名作为参数。

所以我的问题是：有没有办法读取此列表中包含的每个名称，并为每个名称动态创建一个新的SQL任务？



非常感谢您的亲切支持，

Giuseppe

Dear All,

I'm a beginner in SSIS.
I have a list of tables name for which I have to create a specific SQL Tasks. Each SQL Task has to run the same store Procedure, that accepts the table name as parameter.
So my question is: is there a way to read each name contained in this list and for each one create a new SQL Task dynamically?

Many thanks in advance for your kindly support,
Giuseppe

推荐答案

请试试这个。

1 。在包级别创建对象类型变量

2.使用SQL语句创建SQL任务

Please try this.
1. Create an Object Type Variable at Package Level
2. Create SQL Task with SQL Statement as

SELECT 'YourTable1' AS [TableList]
UNION
SELECT 'YourTable2' AS [TableList]
UNION
SELECT 'YourTable3' AS [TableList]
UNION
SELECT 'YourTable4' AS [TableList]

注意：用你的表名更改YourTable *。



3 。将SQL任务结果集设置为完整结果集，并将此结果映射到步骤1中创建的变量

Ref： https://msdn.microsoft.com/en-us/library/ms141689（v = sql.120）的.aspx [<一个href =https://msdn.microsoft.com/en-us/library/ms141689(v=sql.120).aspx\"target =_ blanktitle =New Window> ^ ]



4.在包级别（ParTableName）创建一个String Type变量。

5.创建一个Foreach容器，并将其设置为ADO枚举器和将ADO Object Source变量设置为在Step1中创建的变量

在Variable mappings下，选择在步骤4中创建的变量（其索引将为= 0）

Ref：在SSIS中使用Foreach ADO枚举器 [ ^ ]



6.现在在Foreach中创建一个SQL任务容器。这将对表执行你的商店proc

SQL语句将是：

EXEC [YourStoredProc]？

现在映射一个参数你的SQL任务

参考： https://msdn.microsoft。 com / en-us / library / ms140355（v = sql.120）.aspx [ ^ ]



让我知道它是怎么回事！



谢谢，

Kuthuparakkal

Note: Change YourTable* with your table names.

3. Set the SQL Task Result Set to "Full result set" and map this results to the variable created in step 1
Ref: https://msdn.microsoft.com/en-us/library/ms141689(v=sql.120).aspx[^]

4. Create a String Type variable at Package Level (ParTableName).
5. Create a Foreach Container, and set it as ADO Enumerator and set ADO Object Source variable to variable created in Step1
Under Variable mappings select the variable created in Step 4 (its index will be = 0)
Ref: Using the Foreach ADO Enumerator in SSIS[^]

6. Now create a SQL Task inside Foreach Container. This will execute your store proc against tables
SQL Statement will be:
EXEC [YourStoredProc] ?
Now map a parameter your SQL Task
Ref: https://msdn.microsoft.com/en-us/library/ms140355(v=sql.120).aspx[^]

Let me know how it goes!

Thanks,
Kuthuparakkal

这篇关于SSIS：有没有办法动态生成一些SQL任务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！