SSIS 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 from the Execute SQL Task
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 used by the Execute SQL Task
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.
Sending Multiple Statements in a Batch using 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.
Running Parameterized SQL Commands using the Execute SQL Task
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.
Specifying a Result Set Type for 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.
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
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:
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:
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services