SSIS:尝试在ForEach循环容器内使用输入变量 [英] SSIS: Trying to use an input variable inside a ForEach Loop Container
问题描述
我正在尝试使用变量作为执行SQL任务"中的输入.
我在ForEach容器之外预先设置了对象.在foreach容器中，我设置了所有变量映射.我知道所有操作都正确完成了，因为当我使用脚本任务将这些参数输出到screan时，它可以正常工作.
但是，当我将变量映射"中的这些变量之一设置为输入"，并将参数名称设置为0时，设置执行SQL任务"，然后在SQLStatement中使用此简单脚本:
从SCHDTASK选择名称
WHERE ID =?
运行此程序时出现错误.
"[[执行SQL任务]错误:执行查询"(从SCHDTASK WHERE ID =?中选择名称)失败，并显示以下错误:语法错误，权限冲突或其他非特定错误".可能的失败原因:查询问题，" ResultSet"属性未正确设置，参数未正确设置或连接未正确建立.
如果有人知道我在这里做错了什么，我将不胜感激任何输入.
谢谢！
I am trying to use a variable as an input in an ''Execute SQL Task''.
I pre-set my object outside the ForEach container. In the foreach container I set up all the Variable Mappings. I know all that is done correctly because when I use a Script Task to output these parameters to the screan it works.
But when I set up my ''Execute SQL Task'' with one of these variables in the Variable Mappings as ''Input'', with Parameter Name set to 0, and then have this simple script in the SQLStatement:
SELECT NAME FROM SCHDTASK
WHERE ID = ?
I get an error when I run this.
"[Execute SQL Task] Error: Executing the query "SELECT NAME FROM SCHDTASK WHERE ID = ?" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If anyone has any idea what I am doing wrong here, I would really appreciate any kind of input.
Thank you!
推荐答案
1.首先使用一些静态ID检查SQL
1. First check SQL with some static ID
SELECT NAME FROM SCHDTASK
WHERE ID = 10
如果看不到任何错误，请继续对变量映射进行故障排除.
首先将更改结果设置为无(如果有)，然后验证以下内容.
如果对SQL使用OLDEB conn，则参数名称可以依次为0、1、2.
但是，如果它是ADO.Net conn，请指定参数名称.
参考: http://msdn.microsoft.com/en-us/library/cc280502.aspx [ ^ ]
If you do not see any error on this continue to troubleshoot variable mappings.
First of all change result set to none, if any and verify the following.
If you are using OLDEB conn to SQL then parameter name can be 0, 1, 2 in order.
But if it''s ADO.Net conn, specify the parameter name.
Refer : http://msdn.microsoft.com/en-us/library/cc280502.aspx[^]
这篇关于SSIS:尝试在ForEach循环容器内使用输入变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
