Execute SQL Task in SSIS with Real-Time Scenarios

The Execute SQL Task in SSIS is used to run SQL queries, stored procedures, or DDL/DML commands against a database. It is one of the most commonly used tasks in ETL (Extract, Transform, Load) processes.

---

Key Features of Execute SQL Task

1. Executes T-SQL, PL/SQL, or other SQL statements depending on the connection manager.

2. Can return single-row results, full result sets, or no results.

3. Supports parameterized queries (input/output parameters).

4. Can be used for:

   • Data manipulation (INSERT, UPDATE, DELETE)

   • Calling stored procedures

   • Creating/altering database objects (tables, views, etc.)

   • Logging and auditing ETL operations

   • Truncating tables before loading new data

---

Real-Time Scenarios for Execute SQL Task

1. Truncating a Staging Table Before Data Load

Scenario: Before loading new data into a staging table, you need to clear old records.
Solution: Use an Execute SQL Task to run TRUNCATE TABLE or DELETE.

sql

Copy

Download

TRUNCATE TABLE Staging.Customers;

Configuration:

• Connection: OLE DB/SQL Server Connection Manager

• SQL Statement: TRUNCATE TABLE Staging.Customers;

• Execution: Runs before the Data Flow Task that loads new data.

---

2. Logging ETL Process Start/End Times

Scenario: Track when an SSIS package starts and completes.
Solution: Insert timestamps into a logging table.

sql

Copy

Download

INSERT INTO ETL.AuditLog (PackageName, StartTime, Status) 
VALUES (?, GETDATE(), 'Started');

Configuration:

• Connection: SQL Server Connection Manager

• SQL Statement: Parameterized query (using ? or named parameters).

• Parameters: Map PackageName from an SSIS variable.

---

3. Calling a Stored Procedure for Data Processing

Scenario: A stored procedure aggregates data before loading into a data warehouse.
Solution: Use Execute SQL Task to call the SP.

sql

Copy

Download

EXEC dbo.ProcessSalesData @Year = 2023, @Month = 12;

Configuration:

• Connection: ADO.NET or OLE DB Connection Manager

• SQL Statement: EXEC dbo.ProcessSalesData @Year = ?, @Month = ?

• Parameters: Map @Year and @Month from SSIS variables.

4. Updating a Flag After Successful Data Load

Scenario: After loading data into a target table, update a status flag.
Solution: Run an UPDATE statement.

sql

Copy

Download

UPDATE Config.ETL_Status 
SET LastLoadDate = GETDATE(), IsLoaded = 1 
WHERE TableName = 'Dim_Customers';

Configuration:

• Connection: OLE DB Connection Manager

• SQL Statement: Direct update query.

• Placement: After the Data Flow Task in the Control Flow.

---

5. Dynamic SQL Execution Based on Variables

Scenario: Build a SQL query dynamically using variables.
Solution: Use Expressions to construct the SQL statement.

sql

Copy

Download

SELECT * FROM Sales.Orders 
WHERE OrderDate BETWEEN @StartDate AND @EndDate;

Configuration:

• SQLSourceType: Variable (instead of direct input)

• Expression: Build the query using variables like "SELECT * FROM Sales.Orders WHERE OrderDate BETWEEN '" + @[User::StartDate] + "' AND '" + @[User::EndDate] + "'"

• Parameters: Pass @StartDate and @EndDate as variables.

---

Execute SQL Task Configuration Options

Setting	Description
Connection	Specifies the database connection (OLE DB, ADO.NET, ODBC).
SQL Statement	The query, stored procedure call, or DDL command.
Parameter Mapping	Maps SSIS variables to SQL parameters.
Result Set	Defines if the task returns a result (None, Single Row, Full Result Set, XML).
Bypass Prepare	Improves performance by skipping query preparation.
Time-out	Sets query execution timeout (0 = no timeout).

---

Best Practices

✔ Use parameterized queries instead of dynamic SQL to prevent SQL injection.
✔ Use OLE DB Connection for best performance with SQL Server.
✔ Log errors using event handlers or try-catch logic in stored procedures.
✔ Set a timeout for long-running queries.
✔ Use transactions if multiple SQL statements need to commit/rollback together.

---

Conclusion

The Execute SQL Task is a versatile component in SSIS used for:

• Data preparation (truncate, delete)

• Stored procedure execution

• ETL logging and auditing

• Dynamic SQL execution

• Post-load updates