如何在执行 SQL 任务 SSIS 中将变量作为参数传递？

How to pass variable as a parameter in Execute SQL Task SSIS?

提问by Neo

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

我已经在创建一个临时表中执行了一项执行 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') AND (PortfolioId = 5) AND (stype in ('Index'))使用变量名传递这 3 个参数，我在包中创建了变量，以便我变得动态。

回答by PaulStock

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 语句是存储过程的名称，过程的每个参数都带有问号，如下所示：

Click the parameter mapping in the left column and add each paramter from your stored proc and map it to your SSIS variable:

单击左列中的参数映射并添加存储过程中的每个参数并将其映射到 SSIS 变量：

Now when this task runs it will pass the SSIS variables to the stored proc.

现在，当此任务运行时，它会将 SSIS 变量传递给存储过程。

回答by user2907295

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 连接并浪费了几个小时试图找出查询不起作用或采用参数的原因。上面的解释很有帮助 非常感谢。

回答by Sam

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

回答by Teju MB

SELECT, INSERT, UPDATE, and DELETEcommands 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.

SELECT、INSERT、UPDATE和DELETE命令经常包含 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 命令的示例。INSERT、UPDATE 和 DELETE 语句类似。这些示例使用 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

网

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。

回答by Steve L

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...