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

Question

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

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

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.

Answer 1

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:

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

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