How to pass variable as a parameter in Execute SQL

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

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

Answer 2

The EXCEL and OLED DB connection managers use the parameter names 0 and 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.

Answer 3

Along with @PaulStock's answer, Depending on your connection type, your variable names and SQLStatement/SQLStatementSource Changes

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task

Answer 4

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.

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.

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.

EXCEL, ODBC, and OLEDB

SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?

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.

Answer 5

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.