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:
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).
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
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
The Execute SQL task can save data that is the result of a query. For example, you can run a SELECT statement that retrieved data from a table. Then, by assigning a global variable as an output parameter in the task, you can save the first row returned from the query, populating multiple global variables with the value of each column returned.
The following code example shows you how to put the results of a SELECT statement, run in the Execute SQL task, into multiple global variables. To see the columns that were returned from the query that are stored in the global variables, you can write script in a Microsoft® ActiveX® Script task. The second sample will retrieve and display the four columns returned from the SELECT statement.
To save row values into global variables
To retrieve the row value data
The Execute SQL task can save an entire rowset returned from a SELECT statement and assign it to a single global variable, which can be saved with the package. You can treat such a global variable as a disconnected Microsoft ActiveX Data Objects (ADO) recordset. You can navigate through the recordset, query the recordset, and manipulate data returned from the recordset. You have access to the entire range of ADO methods and properties to use on the recordset. Because you do not have to reconnect to the source table, accessing the rowset stored in the global variable is faster than accessing the original table that the data came from. However, you must consider how frequently the source data is updated when using disconnected recordsets because the data processed by the package may be obsolete.
Note Storing large rowsets into a global variable has the potential to slow package execution and use large amounts of memory. It takes time to fill the global variable with the data, and when you use the global variable as a lookup table in other tasks, large rowsets will take time to loop through, again having performance consequences.
The following code example shows you how to put the entire result set of a SELECT statement, run in the Execute SQL task, into one global variable. To see the data returned from the query that is stored in the global variable, you can write script in an ActiveX Script task. The second sample will retrieve and display the data returned from the query.
To save an entire rowset into a global variable
To retrieve rowset data stored in a global variable
To add the Execute SQL task to a DTS package
Using Global Variables with DTS Packages