Execute SQL Task

Data Transformation Services

Data Transformation Services

Execute SQL Task

With the Execute SQL task, you can run SQL statements during package execution. The Execute SQL task also can save data that is the result of a query. Using the Execute SQL task, you can:

  • Populate multiple global variables.

  • Save the complete rowset returned from the query into one global variable.

  • Drop a table.

  • Re-create fact and dimension tables before loading them.

  • Run stored procedures.

The task can contain either a single SQL statement or multiple SQL statements that execute sequentially. SQL statements can range from being a SELECT command to running a stored procedure.

The SQL statements must be written in the dialect of the source database management system (DBMS).

Sending Multiple Statements in a Batch for Execution

If multiple statements are contained in the task, they can be grouped and executed a batch at a time. To signal the end of a batch, use the GO command. All the SQL statements from one GO command to the next are sent in a batch to the OLE DB provider for execution.

Note  There are restrictions on the kinds of SQL statements that can be grouped together in a batch. For more information, see Batches.

For example, suppose you have three tables: a table containing customer orders; a table containing a daily order summary; and a table of year-to-date orders. After the customer order table is updated, you can use the Execute SQL task to run two stored procedures, one to create the new daily sales summary and the other to update the year-to-date order summary. The following code example shows you how to execute the two stored procedures:

Execute sp_UpdateDailySales
GO
Execute sp_UpdateYTDSales
GO
Running Parameterized Queries

The Execute SQL task can use global variables to populate input parameters in SQL commands, including queries and stored procedures when the source data provider supports parameters. You can write a parameterized query where the value in the SQL statement is filled in at run time by using a question mark as a parameter placeholder. Then, you can map a global variable to the parameter placeholder to specify which global variable will be used at run time in place of the question mark.

To execute a stored procedure with an input parameter