SSIS：有没有办法动态生成一些SQL任务？
亲爱的所有人，
我是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 [ ^ ]
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