I have ssis package in that I'm taking values from flat file and insert it into table.
我有ssis包，我从平面文件中获取值并将其插入到表中。
I have taken one Execute SQL Task in that creating one temptable
在创建一个temptable时，我已经执行了一个执行SQL任务。
CREATE TABLE [tempdb].dbo.##temptable
(
date datetime,
companyname nvarchar(50),
price decimal(10,0),
PortfolioId int,
stype nvarchar(50)
)
Insert into [tempdb].dbo.##temptable (date,companyname,price,PortfolioId,stype)
SELECT date,companyname,price,PortfolioId,stype
FROM ProgressNAV
WHERE (Date = '2011-09-30') AND (PortfolioId = 5) AND (stype in ('Index'))
ORDER BY CompanyName
Now in above query I need to pass (Date = '2011-09-30') AND (PortfolioId = 5) AND (stype in ('Index')) these 3 parameter using variable name I have created variables in package so that I become dynamic.
现在，在上面的查询中，我需要通过(Date = '2011-09-30')和(PortfolioId = 5)以及(stype in ('Index'))这3个参数，使用变量名称，我在包中创建了变量，以使我成为动态的。
5 个解决方案
#1
74
In your Execute SQL Task, make sure SQLSourceType is set to Direct Input, then your SQL Statement is the name of the stored proc, with questionmarks for each paramter of the proc, like so:
在执行SQL任务中，确保将SQLSourceType设置为直接输入，那么您的SQL语句就是存储的proc的名称，并且为proc的每个参数设置问号，如下所示:
Click the parameter mapping in the left column and add each paramter from your stored proc and map it to your SSIS variable:
单击左侧列中的参数映射，并从存储的proc中添加每个参数，并将其映射到SSIS变量:
Now when this task runs it will pass the SSIS variables to the stored proc.
现在，当这个任务运行时，它将把SSIS变量传递给存储的proc。
#2
13
The EXCEL and OLED DB connection managers use the parameter names 0 and 1.
EXCEL和OLED DB连接管理器使用参数名称0和1。
I was using a oledb connection and wasted couple of hours trying to figure out the reason why the query was not working or taking the parameters. the above explanation helped a lot Thanks a lot.
我使用了一个oledb连接，浪费了几个小时试图找出查询不工作的原因或者获取参数。上面的解释很有帮助，非常感谢。
#3
3
SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters that define the conditions each row in the source tables must meet to qualify for an SQL command. Parameters provide the filter values in the WHERE clauses.
选择、插入、更新和删除命令经常包含用于指定过滤器的WHERE子句，这些过滤器定义源表中的每一行必须满足的条件，从而符合SQL命令。参数提供WHERE子句中的筛选值。
You can use parameter markers to dynamically provide parameter values. The rules for which parameter markers and parameter names can be used in the SQL statement depend on the type of connection manager that the Execute SQL uses.
您可以使用参数标记来动态地提供参数值。可以在SQL语句中使用参数标记和参数名称的规则取决于执行SQL使用的连接管理器的类型。
The following table lists examples of the SELECT command by connection manager type. The INSERT, UPDATE, and DELETE statements are similar. The examples use SELECT to return products from the Product table in AdventureWorks2012 that have a ProductID greater than and less than the values specified by two parameters.
下表列出了按连接管理器类型选择命令的示例。插入、更新和删除语句是相似的。示例使用SELECT从AdventureWorks2012中的Product表返回产品，这些产品的ProductID大于且小于两个参数指定的值。
EXCEL, ODBC, and OLEDB
EXCEL,ODBC,OLEDB
SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO
ADO
SELECT * FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET
ADO.NET
SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID
AND ProductID < @parmMaxProductID
The examples would require parameters that have the following names: The EXCEL and OLED DB connection managers use the parameter names 0 and 1. The ODBC connection type uses 1 and 2. The ADO connection type could use any two parameter names, such as Param1 and Param2, but the parameters must be mapped by their ordinal position in the parameter list. The ADO.NET connection type uses the parameter names @parmMinProductID and @parmMaxProductID.
这些示例需要具有以下名称的参数:EXCEL和OLED DB连接管理器使用参数名称0和1。ODBC连接类型使用1和2。ADO连接类型可以使用任意两个参数名，如Param1和Param2，但是参数必须根据它们在参数列表中的序号位置进行映射。ADO。NET连接类型使用参数名@parmMinProductID和@parmMaxProductID。
#4
2
A little late to the party, but this is how I did it for an insert:
聚会有点晚了，但我是这样做的:
DECLARE @ManagerID AS Varchar (25) = 'NA'
DECLARE @ManagerEmail AS Varchar (50) = 'NA'
Declare @RecordCount AS int = 0
SET @ManagerID = ?
SET @ManagerEmail = ?
SET @RecordCount = ?
INSERT INTO...
#5
2
Along with @PaulStock's answer, Depending on your connection type, your variable names and SQLStatement/SQLStatementSource Changes
与@PaulStock的答案一样，取决于您的连接类型、变量名称和SQLStatement/SQLStatementSource更改。
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task 
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task
#1
74
In your Execute SQL Task, make sure SQLSourceType is set to Direct Input, then your SQL Statement is the name of the stored proc, with questionmarks for each paramter of the proc, like so:
在执行SQL任务中，确保将SQLSourceType设置为直接输入，那么您的SQL语句就是存储的proc的名称，并且为proc的每个参数设置问号，如下所示:
Click the parameter mapping in the left column and add each paramter from your stored proc and map it to your SSIS variable:
单击左侧列中的参数映射，并从存储的proc中添加每个参数，并将其映射到SSIS变量:
Now when this task runs it will pass the SSIS variables to the stored proc.
现在，当这个任务运行时，它将把SSIS变量传递给存储的proc。
#2
13
The EXCEL and OLED DB connection managers use the parameter names 0 and 1.
EXCEL和OLED DB连接管理器使用参数名称0和1。
I was using a oledb connection and wasted couple of hours trying to figure out the reason why the query was not working or taking the parameters. the above explanation helped a lot Thanks a lot.
我使用了一个oledb连接，浪费了几个小时试图找出查询不工作的原因或者获取参数。上面的解释很有帮助，非常感谢。
#3
3
SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters that define the conditions each row in the source tables must meet to qualify for an SQL command. Parameters provide the filter values in the WHERE clauses.
选择、插入、更新和删除命令经常包含用于指定过滤器的WHERE子句，这些过滤器定义源表中的每一行必须满足的条件，从而符合SQL命令。参数提供WHERE子句中的筛选值。
You can use parameter markers to dynamically provide parameter values. The rules for which parameter markers and parameter names can be used in the SQL statement depend on the type of connection manager that the Execute SQL uses.
您可以使用参数标记来动态地提供参数值。可以在SQL语句中使用参数标记和参数名称的规则取决于执行SQL使用的连接管理器的类型。
The following table lists examples of the SELECT command by connection manager type. The INSERT, UPDATE, and DELETE statements are similar. The examples use SELECT to return products from the Product table in AdventureWorks2012 that have a ProductID greater than and less than the values specified by two parameters.
下表列出了按连接管理器类型选择命令的示例。插入、更新和删除语句是相似的。示例使用SELECT从AdventureWorks2012中的Product表返回产品，这些产品的ProductID大于且小于两个参数指定的值。
EXCEL, ODBC, and OLEDB
EXCEL,ODBC,OLEDB
SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO
ADO
SELECT * FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET
ADO.NET
SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID
AND ProductID < @parmMaxProductID
The examples would require parameters that have the following names: The EXCEL and OLED DB connection managers use the parameter names 0 and 1. The ODBC connection type uses 1 and 2. The ADO connection type could use any two parameter names, such as Param1 and Param2, but the parameters must be mapped by their ordinal position in the parameter list. The ADO.NET connection type uses the parameter names @parmMinProductID and @parmMaxProductID.
这些示例需要具有以下名称的参数:EXCEL和OLED DB连接管理器使用参数名称0和1。ODBC连接类型使用1和2。ADO连接类型可以使用任意两个参数名，如Param1和Param2，但是参数必须根据它们在参数列表中的序号位置进行映射。ADO。NET连接类型使用参数名@parmMinProductID和@parmMaxProductID。
#4
2
A little late to the party, but this is how I did it for an insert:
聚会有点晚了，但我是这样做的:
DECLARE @ManagerID AS Varchar (25) = 'NA'
DECLARE @ManagerEmail AS Varchar (50) = 'NA'
Declare @RecordCount AS int = 0
SET @ManagerID = ?
SET @ManagerEmail = ?
SET @RecordCount = ?
INSERT INTO...
#5
2
Along with @PaulStock's answer, Depending on your connection type, your variable names and SQLStatement/SQLStatementSource Changes
与@PaulStock的答案一样，取决于您的连接类型、变量名称和SQLStatement/SQLStatementSource更改。
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task