File System Task in SSIS with Real-Time Scenarios

The File System Task in SSIS is used to perform file and folder operations such as copying, moving, deleting, renaming, and setting file attributes. It is commonly used in ETL (Extract, Transform, Load) processes to manage files before or after data processing.

---

Key Features of the File System Task

1. Operations Supported:

   • Copy – Copy a file/folder to a new location.

   • Move – Move a file/folder to a new location.

   • Delete – Remove a file/folder.

   • Rename – Change the name of a file/folder.

   • Create Directory – Make a new folder.

   • Set Attributes – Modify file attributes (Read-only, Hidden, etc.).

2. Works with:

   • Local files

   • Network shared files (UNC paths)

   • FTP/SFTP (when combined with other tasks)

3. No data transformation – Only file/folder manipulation.

---

Real-Time Scenarios for File System Task

1. Moving Processed Files to an Archive Folder

Scenario: After extracting data from a CSV file, move it to an archive directory to avoid reprocessing.
Solution: Use the File System Task with Move operation.

Configuration:

• Operation: Move file

• Source Connection: C:\ETL\Source\sales_data.csv

• Destination Connection: C:\ETL\Archive\sales_data_20240501.csv

• Usage: After the Data Flow Task that processes the file.

---

2. Deleting Old Log Files

Scenario: Log files older than 30 days should be removed to save disk space.
Solution: Use a Script Task to identify old files + File System Task to delete them.

Configuration:

• Operation: Delete file

• Source Connection: C:\Logs\*.log (with a ForEach Loop to iterate files)

• Condition: Delete if LastModifiedDate < (Today - 30 days)

---

3. Copying a File Before Processing (Backup)

Scenario: Before processing a file, create a backup copy in case of failures.
Solution: Use File System Task to Copy the file.

Configuration:

• Operation: Copy file

• Source: \\Shared\Incoming\orders.xlsx

• Destination: \\Shared\Backup\orders_backup.xlsx

• Usage: Before the Data Flow Task that reads the file.

---

 

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.