Execute SQL Task

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.

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). For more information, see Using Queries in Packages.

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 box to type SQL statements, or use Query Builder, a graphical user interface for creating SQL queries. For more information, see Execute SQL Task Editor (General Page) and Query Builder.

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.

For information on using parameters and return codes in the Execute SQL task, see Working with Parameters and Return Codes in the Execute SQL Task.

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.

For information on retrieving result sets from SQL commands in the Execute SQL task, see Working with Result Sets in the 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.

Troubleshooting the Execute SQL Task

You can log the calls that the Execute SQL task makes to external data providers. You can use this logging capability to troubleshoot the SQL commands that the Execute SQL task runs. To log the calls that the Execute SQL task makes to external data providers, enable package logging and select the Diagnostic event at the package level. For more information, see Troubleshooting Package Execution.

Sometimes a 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 errors of RAISERROR or PRINT statements. Whether the task ignores errors in result sets that occur after the first result set depends on the type of connection manager that is used:

• When you use OLE DB and ADO connection managers, the task ignores the result sets that occur after the first result set. Therefore, with these connection managers, the task ignores an error returned by an SQL command or a stored procedure when the error is not part of the first result set.

• When you use ODBC and ADO.NET connection managers, the task does not ignore result sets that occur after the first result set. With these connection managers, the task will fail with an error when a result set other than the first result set contains an error.

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:

• How to: Set the Properties of a Task or Container

Configuring the Execute SQL Task Programmatically

See Also

Tasks

Concepts