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.

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.

Note:
Valid SQL statements written outside the Execute SQL task may not be parsed successfully by the Execute SQL task.

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.
  

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.

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.

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.

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.

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

• OLE DB and ADO 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 a stored procedure when the error is not part of the first result set.
  
• ODBC and ADO.NET connection managers do not ignore result sets that occur after the first result set. These connection managers will fail with an error when a result set other than the first result set contains an error.
  

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

Tasks

Concepts

Other Resources

Help and Information