Skip to content

map-query-parameters-to-variables-in-an-execute-sql-task.md

Latest commit

 

History

History
105 lines (72 loc) · 5.26 KB

map-query-parameters-to-variables-in-an-execute-sql-task.md

File metadata and controls

105 lines (72 loc) · 5.26 KB
title Map Query Parameters to Variables in an Execute SQL Task | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology integration-services
ms.topic conceptual
helpviewer_keywords
queries [Integration Services], parameter mapping
parameterized SQL commands [Integration Services]
parameters [Integration Services]
mapping query parameters to variables [Integration Services]
Execute SQL task [Integration Services]
variables [Integration Services], mapping parameters to
ms.assetid 6a164349-dfcf-4995-80bc-d4e7aee52a83
author janinezhang
ms.author janinez
manager craigg

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

  1. In [!INCLUDEssBIDevStudioFull], open the [!INCLUDEssISnoversion] package you want to work with.

  2. In Solution Explorer, double-click the package to open it.

  3. Click the Control Flow tab.

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

  5. Double-click the Execute SQL task.

  6. 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 [!INCLUDEssSampleDBUserInputNonLocal] 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 < ?
    [!INCLUDEvstecado] 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.

  7. Click Parameter Mapping.

  8. To add a parameter mapping, click Add.

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

  10. From the Variable Name list, select a variable. For more information, see Add, Delete, Change Scope of User-Defined Variable in a Package.

  11. In the Direction list, specify if the parameter is an input, an output, or a return value.

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

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

  14. Click OK.

See Also

Execute SQL Task
Parameters and Return Codes in the Execute SQL Task
Integration Services (SSIS) Variables