Execute SQL Task

The Execute SQL Task enables you to execute SQL statements against a variety of data sources. It is a fundamental component for interacting with databases within SQL Server Integration Services (SSIS) packages.

Overview

This task can execute:

Single SQL statements.
Stored procedures.
SQL queries that return a single row.
SQL queries that return multiple rows.

The results of the SQL statement can be used in several ways, such as:

Mapping the result to a variable.
Saving the result to a file.
Returning the result set to the calling application.

Key Properties

The Execute SQL Task has several important properties that you can configure:

Connection: Specifies the connection manager to use for connecting to the data source.
SQLStatement: Contains the SQL statement or stored procedure call to be executed.
IsStoredProcedure: A boolean value indicating whether the SQLStatement is a stored procedure call.
BypassPrepare: If true, the task bypasses the prepare step and directly executes the SQL. This can improve performance for simple statements but might pose a security risk if the SQL statement is not parameterized.
ResultSet: Specifies the type of result set the task returns. Options include None, FirstRow, Full result set, and XML.
Parameters: Allows you to map SSIS variables to parameters in your SQL statement, enabling dynamic query execution and preventing SQL injection vulnerabilities.

Configuring the Execute SQL Task

To configure the Execute SQL Task:

Drag and drop the Execute SQL Task from the SSIS Toolbox onto your Control Flow designer.
Double-click the task to open the Execute SQL Task Editor.
On the General page, select a Connection to your database.
In the SQLStatement property, enter your SQL query or stored procedure name.
If you are executing a stored procedure, set IsStoredProcedure to True.
Configure the ResultSet property based on how you intend to use the results.
Navigate to the Parameter Mapping and Result Set tabs to define how variables are passed to and from the SQL statement.

Note: Always use parameterized queries or stored procedures with parameters when dealing with user-supplied input to prevent SQL injection attacks.

Example Usage

Example 1: Executing a simple query and returning the first row

Scenario: Retrieve the count of customers from a table.

SELECT COUNT(*) AS CustomerCount FROM Customers;

Configuration:

Connection: Your database connection manager.
SQLStatement: SELECT COUNT(*) AS CustomerCount FROM Customers;
ResultSet: First row
Result Set Mapping: Map the CustomerCount column to an SSIS variable (e.g., User::CustomerCountVar).

Example 2: Executing a stored procedure with an output parameter

Scenario: Call a stored procedure that updates a record and returns a status code.

EXEC usp_UpdateProductPrice @ProductID = ?, @NewPrice = ?, @StatusCode = ? OUTPUT;

Configuration:

Connection: Your database connection manager.
SQLStatement: EXEC usp_UpdateProductPrice @ProductID = ?, @NewPrice = ?, @StatusCode = ? OUTPUT;
IsStoredProcedure: True
Parameter Mapping:
- Variable: User::ProductIdVar, Direction: Input, Data Type: Int, Parameter Name: @ProductID
- Variable: User::NewPriceVar, Direction: Input, Data Type: Decimal, Parameter Name: @NewPrice
- Variable: User::StatusCodeVar, Direction: Output, Data Type: Int, Parameter Name: @StatusCode
ResultSet: None (since we are only interested in the output parameter)

Important: The order of parameters in the SQLStatement must match the order in the Parameter Mapping tab. Ensure the data types are compatible.