Map Query Parameters to Variables in an Execute SQL Task
This topic describes how to use a parameterized SQL statement in the Execute SQL task and create mappings between variables and the parameters in the SQL statement.
To learn more about the Execute SQL task, the parameter markers, and parameter names you use with different connection types, see Execute SQL Task and Parameters and Return Codes in the Execute SQL Task.
To map a query parameter to a variable
In SQL Server Data Tools (SSDT), open the Integration Services package you want to work with.
In Solution Explorer, double-click the package to open it.
Click the Control Flow tab.
If the package does not already include an Execute SQL task, add one to the control flow of the package. For more information, see Add or Delete a Task or a Container in a Control Flow.
Double-click the Execute SQL task.
Provide a parameterized SQL command in one of the following ways:
Use direct input and type the SQL command in the SQLStatement property.
Use direct input, click Build Query, and then create an SQL command using the graphical tools that the Query Builder provides.
Use a file connection and then reference the file that contains the SQL command.
Use a variable and then reference the variable that contains the SQL command.
The parameter markers that you use in parameterized SQL statements depend on the connection type that the Execute SQL task uses.
Connection type
Parameter marker
ADO
?
ADO.NET and SQLMOBILE
@<parameter name>
ODBC
?
EXCEL and OLE DB
?
The following table lists examples of the SELECT command by connection manager type. Parameters provide the filter values in the WHERE clauses. 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.
Connection type
SELECT syntax
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
For examples of using parameters with stored procedures, see Parameters and Return Codes in the Execute SQL Task.
Click Parameter Mapping.
To add a parameter mapping, click Add.
Provide a name in the Parameter Name box.
The parameter names that you use depend on the connection type that the Execute SQL task uses.
Connection type
Parameter name
ADO
Param1, Param2, …
ADO.NET and SQLMOBILE
@<parameter name>
ODBC
1, 2, 3, …
EXCEL and OLE DB
0, 1, 2, 3, …
From the Variable Name list, select a variable. For more information, see Add, Delete, Change Scope of User-Defined Variable in a Package.
In the Direction list, specify if the parameter is an input, an output, or a return value.
In the Data Type list, set the data type of the parameter.
Important
The data type of the parameter must be compatible with the data type of the variable.
Repeat steps 8 through 11 for each parameter in the SQL statement.
Important
The order of parameter mappings must be the same as the order in which the parameters appear in the SQL statement.
Click OK.