Execute SQL Task
The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Execute SQL task for the following purposes:
- Truncate a table or view in preparation for inserting data.
- Create, alter, and drop database objects such as tables and views.
- Re-create fact and dimension tables before loading data into them.
- Run stored procedures.
- Save the rowset returned from a query into a variable.
You can configure the Execute SQL task in the following ways:
- Specify the type of connection manager to use to connect to a database.
- Specify the type of result set that the SQL statement returns.
- Specify a time-out for the SQL statements.
- Specify the source of the SQL statement.
- Indicate whether the task skips the prepare phase for the SQL statement.
- If you use the ADO connection type, you must indicate whether the SQL statement is a stored procedure. For other connection types, this property is read-only and its value is always false.
The Execute SQL task can be used in combination with the Foreach Loop and For Loop containers to run multiple SQL statements. These containers implement repeating control flows in a package and they can run the Execute SQL task repeatedly. For example, using the Foreach Loop container, a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.
Connecting to a Data Source
The Execute SQL task can use different types of connection managers to connect to the data source where it runs the SQL statement or stored procedure. The task can use the connection types listed in the following table.
| Connection type | Connection manager |
|---|---|
EXCEL |
|
OLE DB |
|
ODBC |
|
ADO |
|
ADO.NET |
|
SQLMOBILE |
Creating SQL Statements
The source of the SQL statements used by this task can be a task property that contains a statement, a connection to a file that contains one or multiple statements, or the name of a variable that contains a statement. The SQL statements must be written in the dialect of the source database management system (DBMS).
If the SQL statements are stored in a file, the task uses a File connection manager to connect to the file. For more information, see File Connection Manager.
In SSIS Designer, you can use the Execute SQL Task Editor dialog to type SQL statements, or use Query Builder, a graphical user interface for creating SQL queries.
Note
Valid SQL statements written outside the Execute SQL task may not be parsed successfully by the Execute SQL task.
Sending Multiple Statements in a Batch
If you include multiple statements in an Execute SQL task, you can group them and run them as a batch. To signal the end of a batch, use the GO command. All the SQL statements between two GO commands are sent in a batch to the OLE DB provider to be run. The SQL command can include multiple batches separated by GO commands.
There are restrictions on the kinds of SQL statements that you can group in a batch. For more information, see Batches of Statements.
If the Execute SQL task runs a batch of SQL statements, the following rules apply to the batch:
- Only one statement can return a result set and it must be the first statement in the batch.
- If the result set uses result bindings, the queries must return the same number of columns. If the queries return a different number of columns, the task fails. However, even if the task fails, the queries that it runs, such as DELETE or INSERT queries, may succeed.
- If the result bindings use column names, the query must return columns that have the same names as the result set names that are used in the task. If the columns are missing, the task fails.
- If the task uses parameter binding, all the queries in the batch must have the same number and types of parameters.
Running Parameterized SQL Commands
SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.
Note
You can use parameters in an Execute SQL task only if the data provider supports them.
Parameters in SQL commands, including queries and stored procedures, are mapped to user-defined variables that are created within the scope of the Execute SQL task, a parent container or within the scope of the package. The values of variables can be set at design time or populated dynamically at run time. You can also map parameters to system variables. For more information, see Integration Services Variables and System Variables.
Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers. For example, the ADO.NET connection manager type requires that the SQL command uses a parameter marker in the format @varParameter, whereas OLE DB connection type requires the question mark (?) parameter marker.
The names that you can use as parameter names in the mappings between variables and parameters also vary by connection manager type. For example, the ADO.NET connection manager type uses a user-defined name with a @ prefix, whereas the OLE DB connection manager type requires that you use the numeric value of a 0-based ordinal as the parameter name.
The following table summarizes the requirements for SQL commands for the connection manager types that the Execute SQL task can use.
| Connection type | Parameter marker | Parameter name | Example SQL command |
|---|---|---|---|
ADO |
? |
Param1, Param2, … |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? |
ADO.NET |
@<parameter name> |
@<parameter name> |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID |
ODBC |
? |
1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? |
EXCEL and OLE DB |
? |
0, 1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? |
Parameters with ADO.NET Connection Managers
ADO.NET connection managers require that the SQL command use parameter names as parameter markers. This means that variables can be mapped directly to parameters. For example, the variable @varName is mapped to the parameter named @parName and provides a value to the parameter @parName.
Parameters with EXCEL, ODBC, and OLE DB Connection Managers
EXCEL, ODBC, and OLE DB connection managers require that the SQL command use question marks (?) as parameter markers and 0-based or 1-based numeric values as parameter names. If the Execute SQL task uses the ODBC connection manager, the parameter name that maps to the first parameter in the query is named 1; otherwise, the parameter is named 0. For subsequent parameters, the numeric value of the parameter name indicates the parameter in the SQL command that the parameter name maps to. For example, the parameter named 3 maps to the third parameter, which is represented by the third question mark (?) in the SQL command.
To provide values to parameters, variables are mapped to parameter names and the Execute SQL task uses the ordinal value of the parameter name to load values from variables to parameters.
If the Execute task uses the OLE DB connection type, the BypassPrepare property of the task is available. You should set this property to true if the Execute SQL task uses SQL statements with parameters.
When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.
Depending on the provider that the connection manager uses, some OLE DB data types may not be supported. For example, the Excel driver recognizes only a limited set of data types. For more information about the behavior of the Jet provider with the Excel driver, see Excel Source.
Parameters with ADO Connection Managers
ADO connection managers require that the SQL command use question marks (?) as parameter markers, but you can use any user-defined name, except for integer values, as parameter names.
To provide values to parameters, variables are mapped to parameter names and the Execute SQL task uses the ordinal value of the parameter name in the parameter list to load values from variables to parameters.
Using Parameters with WHERE Clauses
SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters that define the conditions each row in the source tables must meet to qualify for an SQL command. Parameters provide the filter values in the WHERE clauses.
You can use parameter markers to dynamically provide parameter values. The rules for which parameter markers and parameter names can be used in the SQL statement depend on the type of connection manager that the Execute SQL uses.
The following table lists examples of the SELECT command by connection manager type. The INSERT, UPDATE, and DELETE statements are similar. The examples use SELECT to return products from the Product table in AdventureWorks that have a ProductID greater than and less than the values specified by two parameters.
| Connection type | SELECT syntax |
|---|---|
EXCEL, ODBC, and OLEDB |
|
ADO |
|
ADO.NET |
|
The examples would require parameters that have the following names:
- The EXCEL and OLED DB connection managers use the parameter names 0 and 1. The ODBC connection type uses 1 and 2.
- The ADO connection type could use any two parameter names, such as Param1 and Param2, but the parameters must be mapped by their ordinal position in the parameter list.
- The ADO.NET connection type uses the parameter names @parmMinProductID and @parmMaxProductID.
Using Parameters with Stored Procedures
SQL commands that run stored procedures can also use parameter mapping. The rules for how to use parameter markers and parameter names depends on the type of connection manager that the Execute SQL uses, just like the rules for parameterized queries.
The following table lists examples of the EXEC command by connection manager type. The examples run the uspGetBillOfMaterials stored procedure in AdventureWorks. The stored procedure uses the @StartProductID and @CheckDate input parameters.
| Connection type | EXEC syntax |
|---|---|
EXCEL and OLEDB |
|
ODBC |
For more information about ODBC call syntax, see the topic, Procedure Parameters, in the ODBC Programmer's Reference in the MSDN Library. |
ADO |
If IsQueryStoredProcedure is set to False, If IsQueryStoredProcedure is set to True, |
ADO.NET |
If IsQueryStoredProcedure is set to False, If IsQueryStoredProcedure is set to True, |
To use output parameters, the syntax requires that the OUTPUT keyword follow each parameter marker. For example, EXEC myStoredProcedure ? OUTPUT.
For more information about using input and output parameters with Transact-SQL stored procedures, see Parameters (Database Engine), Returning Data by Using OUTPUT Parameters, and EXECUTE (Transact-SQL).
Getting Values of Return Codes
A stored procedure can return an integer value, called a return code, to indicate the execution status of a procedure. To implement return codes in the Execute SQL task, you use parameters of the ReturnValue type.
The following table lists by connection type some examples of EXEC commands that implement return codes. All examples use an input parameter. The rules for how to use parameter markers and parameter names with ReturnValue parameters are the same rules that apply to Input and Output parameter types.
Some syntax does not support parameter literals. In that case, you must provide the parameter value by using a variable.
| Connection type | EXEC syntax |
|---|---|
EXCEL and OLEDB |
|
ODBC |
For more information about ODBC call syntax, see the topic, Procedure Parameters, in the ODBC Programmer's Reference in the MSDN Library. |
ADO |
If IsQueryStoreProcedure is set to False, If IsQueryStoreProcedure is set to True, |
ADO.NET |
Set IsQueryStoreProcedure is set to True.
|
For more information about using return codes with Transact-SQL stored procedures, see Returning Data by Using a Return Code and RETURN (Transact-SQL).
Specifying a Result Set Type
Depending on the type of SQL command, a result set may or may not be returned to the Execute SQL task. For example, a SELECT statement typically returns a result set, but an INSERT statement does not. The result set from a SELECT statement can contain zero rows, one row, or many rows. Stored procedures can also return an integer value, called a return code, that indicates the execution status of the procedure. In that case, the result set consists of a single row.
The Execute SQL task supports the following types of result sets:
- The None result set is used when the query returns no results. For example, this result set is used for queries that add, change, and delete records in a table.
- The Single row result set is used when the query returns only one row. For example, this result set is used for a stored procedure that returns a return code, or for a SELECT statement that returns a count or a sum.
- The Full result set result set is used when the query returns multiple rows. For example, this result set is used for a SELECT statement that retrieves all the rows in a table.
- The XML result set is used when the query returns a result set in an XML format. For example, this result set is used for a SELECT statement that includes a FOR XML clause.
If the Execute SQL task uses the Full result set result set and the query returns multiple rowsets, the task returns only the first rowset. If this rowset generates an error, the task reports the error. If other rowsets generate errors, the task does not report them.
The Execute SQL task converts to strings any values returned by the SQL statement that are not already strings. For example, values that have SQL Server uniqueidentifier, bigint, decimal, or numeric data types are converted to strings.
Populating a Variable with a Result Set
You can bind the result set that a query returns to a user-defined variable, if the result set type is a single row, a rowset, or XML.
If the result set type is Single row, you can bind a column in the return result to a variable by using the column name as the result set name, or you can use the ordinal position of the column in the column list as the result set name. For example, the result set name for the query SELECT Color FROM Production.Product WHERE ProductID = ? could be Color or 0. If the query returns multiple columns and you want to access the values in all columns, you must bind each column to a different variable. If you map columns to variables using numbers as result set names, the numbers reflect the order in which the columns appear in the column list of the query. For example, in the query SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?, you use 0 for the Color column and 1 for the ListPrice column. The ability to use a column name as the name of a result set depends on the provider that the task is configured to use. Not all providers make column names available.
Some queries that return a single value may not include column names. For example, the statement SELECT COUNT (*) FROM Production.Product returns no column name. You can access the return result using the ordinal position, 0, as the result name. To access the return result by column name, the query must include an AS <alias name> clause to provide a column name. The statement SELECT COUNT (*)AS CountOfProduct FROM Production.Product, provides the CountOfProduct column. You can then access the return result column using the CountOfProduct column name or the ordinal position, 0.
If the result set type is Full result set or XML, you must use 0 as the result set name.
When you map a variable to a result set with the Single row result set type, the variable must have a data type that is compatible with the data type of the column that the result set contains. For example, a result set that contains a column with a String data type cannot map to a variable with a numeric data type. An XML result set can map only to a variable with the String or Object data type. If the variable has the String data type, the Execute SQL task returns a string and the XML source can consume the XML data. If the variable has the Object data type, the Execute SQL task returns a Document Object Model (DOM) object. A Full result set must map to a variable of the Object data type. The return result is a rowset object. You can write custom tasks that navigate the rowset object and access information about the columns and the data in the rowset.
The following table summarizes the data types of variables that can be mapped to result sets.
| Result set type | Data type of variable | Type of object |
|---|---|---|
Single row |
Any type that is compatible with the type column in the result set. |
Not applicable |
Full result set |
Object |
If the task uses a native connection manager, including the ADO, OLE DB, Excel, and ODBC connection managers, the returned object is an ADO Recordset. If the task uses a managed connection manager, such as the ADO.NET connection manager, then the returned object is a System.Data.DataSet. |
XML |
String |
String |
XML |
Object |
If the task uses a native connection manager, including the ADO, OLE DB, Excel, and ODBC connection managers, the returned object is an MSXML6.IXMLDOMDocument. If the task uses a managed connection manager, such as the ADO.NET connection manager, the returned object is a System.Xml.XmlDocument. |
The variable can be defined in the scope of the Execute SQL task or the package. If the variable has package scope, the result set is available to other tasks and containers within the package, and is available to any packages run by the Execute Package or Execute DTS 2000 Package tasks.
When you map a variable to a Single row result set, values that the SQL statement returns and that are not already strings might be converted to strings. Whether this conversion occurs, or whether this conversion is implicit or explicit, depends on the type of connection manager that is used:
- With an ADO.NET connection manager, conversion does not occur.
- With an ADO or ODBC connection manager, this conversion occurs implicitly.
- With an OLE DB or Excel connection manager, the connection manager explicitly converts values of the following types, DBTYPE_I8, DBTYPE_UI8, DBTYPE_NUMERIC, DBTYPE_GUID, and DBTYPE_BYTES, to strings.
For information about loading a result set into a variable, see How to: Map Result Sets to Variables in an Execute SQL Task.
Custom Log Entries Available on the Execute SQL Task
The following table describes the custom log entry for the Execute SQL task. For more information, see Implementing Logging in Packages and Custom Messages for Logging.
| Log entry | Description |
|---|---|
ExecuteSQLExecutingQuery |
Provides information about the execution phases of the SQL statement. Log entries are written when the task acquires connection to the database, when the task starts to prepare the SQL statement, and after the execution of the SQL statement is completed. The log entry for the prepare phase includes the SQL statement that the task uses. |
Troubleshooting the Execute SQL Task
Starting in Microsoft SQL Server 2005 Service Pack 2 (SP2), you are able to log the calls that the Execute SQL task makes to external data providers. You can use this new logging capability to troubleshoot the SQL commands that the Execute SQL task runs. To log the calls that the Execute SQL task makes to an external data provider, enable package logging and select the Diagnostic event at the package level. For more information, see Troubleshooting Package Execution.
Sometimes an SQL command or stored procedure returns multiple result sets. These result sets include not only rowsets that are the result of SELECT queries, but single values that are the result of RAISERROR or PRINT statements. Except for the ODBC connection manager, all other connection managers ignore the result sets that occur after the first result set. Therefore, these connection managers ignore an error returned by an SQL command or stored procedure when the error is not part of the first result set.
Configuring the Execute SQL Task
You can set properties programmatically or through SSIS Designer.
For more information about the properties that you can set in SSIS Designer, click one of the following topics:
- Execute SQL Task Editor (General Page)
- Execute SQL Task Editor (Parameter Mapping Page)
- Execute SQL Task Editor (Result Set Page)
- Expressions Page
For more information about how to set these properties in SSIS Designer, click the following topic:
Configuring the Execute SQL Task Programmatically
For more information about programmatically setting these properties, click the following topic:
See Also
Tasks
How to: Map Query Parameters to Variables in an Execute SQL Task
Concepts
Foreach Loop Container
For Loop Container
Integration Services Tasks
Creating Package Control Flow
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
15 September 2007 |
|
12 December 2006 |
|
17 July 2006 |
|
14 April 2006 |
|
5 December 2005 |
|