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.

Connection Managers in SSIS: A Complete Guide

 Connection Managers in SSIS define how your package connects to external data sources and destinations. They are reusable across tasks and simplify data integration workflows. Below is a structured breakdown:

1. What is a Connection Manager?

A Connection Manager is a logical representation of a connection to:

• Databases (SQL Server, Oracle, MySQL)

• Flat Files (CSV, TXT, Excel)

• Web Services (HTTP, SOAP)

• Cloud Services (Azure Blob, AWS S3)

Key Features:

✔ Centralized configuration (Change once, update everywhere).
✔ Supports multiple data sources in a single package.
✔ Runtime flexibility (Expressions & parameters can modify connections).

---

2. Types of Connection Managers

Type	Purpose	Example Use Case
OLE DB	Connect to SQL Server, Oracle	Extract data from SQL tables
ADO.NET	Modern .NET data providers	Connect to PostgreSQL, MySQL
Flat File	Read/Write CSV, TXT files	Import sales data from a CSV
Excel	Read/Write Excel files	Load data from XLSX reports
FTP	Transfer files via FTP	Download daily inventory files
HTTP	Access web services/REST APIs	Call a weather API
Azure	Connect to Azure services	Blob Storage, SQL Database

---

3. How to Create a Connection Manager?

Step 1: Open SSIS Package

• In SQL Server Data Tools (SSDT), open your package.

Step 2: Add a Connection Manager

1. Right-click in the Connection Managers tray.

2. Select "New Connection" → Choose type (e.g., OLE DB, Flat File).

Step 3: Configure Properties

Property	Description
Name	Logical name (OLEDB_SQLServer, FF_SourceFile)
ServerName	SQL Server instance (localhost\SQLEXPRESS)
FilePath	Path to CSV/Excel (C:\Data\Sales.csv)
Authentication	Windows / SQL Login
ConnectionString	Dynamically set via expressions

Example: OLE DB Connection to SQL Server

1. Provider: SQL Server Native Client

2. Server: localhost

3. Database: AdventureWorks

4. Authentication: Windows Integrated

---

4. Dynamic Connections (Using Expressions)

Instead of hardcoding paths, use expressions to modify connections at runtime.

Example: Dynamic File Path

1. Create a variable User::FilePath = "C:\Data\Sales.csv".

2. Right-click the Flat File Connection Manager → Properties.

3. Click Expressions (…) → Select "ConnectionString".

4. Set expression:

   plaintext

   Copy

   Download

   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FilePath]

Now, the path updates based on the variable.

---

5. Common Connection Manager Use Cases

A) SQL Server to Flat File ETL

1. OLE DB Connection → Source (SQL Table).

2. Flat File Connection → Destination (CSV).

B) Excel to SQL Server

1. Excel Connection → Source (Sales.xlsx).

2. OLE DB Connection → Destination (SQL Table).

C) FTP File Download

1. FTP Connection → Download inventory.csv.

2. Flat File Connection → Process the file.

---

6. Best Practices

✅ Use Project-Level Connection Managers (Shared across packages).
✅ Parameterize Connections (Avoid hardcoding server names).
✅ Use Windows Authentication (More secure than SQL Logins).
✅ Test Connections (Right-click → Test Connection).

---

7. Troubleshooting

🔹 "Login Failed" Error?

• Verify credentials (Use RetainSameConnection=True for transactions).

🔹 "File Not Found" Error?

• Check file permissions (SSIS runs under SQL Agent account).

🔹 Slow Performance?

• Optimize batch sizes (OLE DB: DefaultBufferMaxRows=10000).

---

8. Summary Table

Action	Steps
Create a Connection	Right-click → New → Configure
Make Dynamic	Use Expressions (e.g., @[User::Server])
Reuse Connections	Project-Level Connection Managers
Debug Issues	Test Connection + Check Logs

---

Final Thoughts

Connection Managers are the bridge between SSIS and external data sources. By mastering them, you can build flexible, maintainable ETL pipelines.