如何在执行 SQL 任务 SSIS 中将变量作为参数传递? [英] How to pass variable as a parameter in Execute SQL Task SSIS?

问题描述

我有 ssis 包，我从平面文件中获取值并将其插入表中.

I have ssis package in that I'm taking values from flat file and insert it into table.

我在创建一个临时表时执行了一个执行 SQL 任务

I have taken one Execute SQL Task in that creating one temptable

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

现在在上面的查询中我需要传递 (Date = '2011-09-30') AND (PortfolioId = 5) AND (stype in ('Index'))这 3 个使用变量名的参数我在包中创建了变量，以便我变得动态.

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.

推荐答案

在您的 Execute SQL Task 中，确保 SQLSourceType 设置为 Direct Input，然后您的 SQL Statement 是存储过程的名称，每个参数都带有问号过程，像这样:

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:

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

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.

这篇关于如何在执行 SQL 任务 SSIS 中将变量作为参数传递?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！