How to: Map Query Parameters to Variables in an Execute SQL Task

In Business Intelligence Development Studio, 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 How to: 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.

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.

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.

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.

Repeat steps 8 through 11 for each parameter in the SQL statement.

Click OK.