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.

score 108 · Accepted Answer · edited Dec 12 '22 at 17:33

108

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.

edited Dec 12 '22 at 17:33

Paul

4,160
3
30
56

answered Sep 30 '11 at 14:03

PaulStock

11,053
9
49
52

thank you.. what about i'm directly writing above query into 'SQLStatement' and in where clause i used where Date = @[User::Date] – Neo Oct 03 '11 at 08:50
3

yes, i am also not using the SP for the same instead am using the Query itself, how to pass parameter to it? – Teju MB Aug 27 '13 at 08:44
10

I know that's old answer, but I have to ask. ;p You use `OLE DB` in Connection Type, but you are using `@something` as ParameterName. Is it really works? If so - why? Here: https://msdn.microsoft.com/en-us/ms140355.aspx I can read, that I should use `0,1,2,3..` as ParameterName. – Monic May 08 '15 at 13:37
I have same question as @Monic. When I follow the above, and attempt to parse the sqlstatementsource, I get "The query failed to parse. Incorrect syntax near '?'. What am I missing? – missscripty Jul 08 '16 at 16:59
4

If you are using ADO.NET for ConnectionType, replace the ? with the names of the Stored Procedure Parameter Names. – Gordon Bell Feb 07 '17 at 23:01
1

If the Connection Type is OLE DB and you use @named parameters then they will be taken in the order listed, i.e. first ? in query maps to first parameter in the list and so on. – ovinophile Apr 12 '17 at 15:35
If you still have Parsing errors, it can be difficult to spot. Check that you have >= rather than => for instance, sometimes you can overlook this. Take it back to basics and use profiler to review the executed statement: exec sp_executesql N'Select @P1 as Test',N'@P1 datetime2(0)','2018-02-01 14:00:07' – tommylux Feb 06 '18 at 12:26

score 23 · Answer 2 · answered Oct 22 '13 at 13:33

23

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.

answered Oct 22 '13 at 13:33

user2907295

239
2
2

1

No, this is a correct answer. Also had the same problem, could only make it work with parameter names 0, 1, and so on. – fancyPants Oct 22 '13 at 14:14
2

And ODBC is 1 based. – Gordon Bell Feb 07 '17 at 23:03
Thanks, was scratching my head wondering why it wasn't working until I saw this – BStone Aug 02 '17 at 09:52

score 21 · Answer 3 · answered Oct 13 '17 at 23:11

21

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

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

answered Oct 13 '17 at 23:11

Sam

1,264
14
19

score 7 · Answer 4 · edited May 21 '14 at 15:53

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.

any thoughts why mine won't parse correctly? I have it set up according to ado.net. — missscripty, Jul 08 '16 at 17:11
Use Parameter Names with @ signs instead of ? for ADO.NET ConnectionType. @missscripty — Gordon Bell, Feb 07 '17 at 23:04

score 5 · Answer 5 · answered Jan 23 '15 at 15:30

5

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

answered Jan 23 '15 at 15:30

Steve L

51
1
1

2

There's no need for your set statement here. your setting the parameters with the equals sign – Nate S. Oct 22 '15 at 13:31
1

I think the idea is that the `DECLARE` statements initialize the variables to default (wrong) values, and the `SET` statements overwrite the defaults to the values that were passed in. – mbbush Jun 23 '17 at 18:01
`DECLARE @variable varchar(10) SET @variable = ?` -> this is THE way to use parameters multiple time. Clean and easy. – KryptonLC Jul 22 '19 at 11:35

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

5 Answers5

Linked

Related