您如何在SSIS中调用存储过程? [英] How do you call a Stored Procedure in SSIS?
问题描述
我正在尝试创建一个SSIS包,该包从表中查询数据,并每行都在另一个数据库中调用存储过程.
I am trying to create an SSIS package that queries data from a table, and calls a stored procedure in another database with each row.
在我的旧DTS包中,我正在这样做:
In my old DTS package, I was doing this:
EXEC myStoredProcedure ?, ?, ?
...然后我映射了参数.但是,在SSIS中,我不知道如何进行这项工作.
...and then I mapped the parameters. However, in SSIS, I can't figure out how to make this work.
我有一个数据流任务,该任务首先运行数据查询.它将数据传递到OLE DB目标.我将数据访问模式设置为"SQL命令",但是当我尝试在上面的SQL中输入内容时,在解析SQL时会收到无效参数计数".我无法进入映射"屏幕.有什么想法吗?
I have a Data Flow task, which first runs a query for the data. It passes the data to an OLE DB Destination. I set the Data access mode to "SQL command", but when I try to put in the SQL above, I get "Invalid Parameter Count" when it parses the SQL. I can't get to the Mappings screen. Any ideas?
推荐答案
在数据流中,OLE DB Command
可用于为数据流中的每一行执行一条SQL语句-(
In the Data Flow, the OLE DB Command
can be used to execute a SQL statement for each row in a dataflow - (MSDN documentation)
或者,您可以将源结果集存储在数据类型为object的变量中,并在控制流中使用Foreach Loop
容器(示例
Alternatively, you can store the source result set in a variable of data type object and use a Foreach Loop
container in the Control Flow (example here).
这篇关于您如何在SSIS中调用存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!