Share via

  • Article

How to: 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 Working with Parameters and Return Codes in the Execute SQL Task.

To map a query parameter to a variable

  1. In Business Intelligence Development Studio, open the Integration Services 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 How to: 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

    ?

  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 How to: Add or Delete a Variable in a Package by Using the Variables Window.

  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.