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.

 

Tasks and Containers in SSIS

In SQL Server Integration Services (SSIS), Tasks and Containers are fundamental components of the Control Flow, which defines the workflow and execution logic of an SSIS package.

---

1. Tasks in SSIS

Tasks are individual units of work that perform specific operations. SSIS provides a variety of built-in tasks for different purposes.

Common SSIS Tasks

Task	Description
Execute SQL Task	Runs SQL statements (SELECT, INSERT, UPDATE, DELETE, stored procedures).
Data Flow Task	Contains the ETL logic (Extract, Transform, Load) using sources, transformations, and destinations.
File System Task	Performs file operations (copy, move, delete, rename).
Execute Package Task	Runs another SSIS package (used for modular design).
Script Task	Executes custom .NET (C#/VB) code for complex logic.
Send Mail Task	Sends emails via SMTP (useful for notifications).
FTP Task	Downloads/uploads files via FTP.
Web Service Task	Calls a web service (SOAP/REST).
Execute Process Task	Runs an external executable (e.g., .exe, .bat).
XML Task	Processes XML files (XSLT, XPath, validation).
Expression Task (SQL 2016+)	Evaluates an expression and stores the result in a variable.

---

2. Containers in SSIS

Containers are used to group tasks, control execution flow, and apply looping or transaction logic.

Types of Containers

Container	Description
Sequence Container	Groups tasks into logical blocks and executes them sequentially. Useful for organizing complex workflows.
For Loop Container	Executes tasks in a loop (like a for loop in programming) based on a condition.
Foreach Loop Container	Iterates over a collection (files in a folder, rows in a table, etc.) and executes tasks for each item.
Task Host Container (Implicit)	Wraps individual tasks (not explicitly visible in SSIS Designer).

---

Common Use Cases for Containers

1. Sequence Container

   • Group related tasks (e.g., "Load Dimension Tables" and "Load Fact Tables").

   • Apply transactions to multiple tasks.

2. For Loop Container

   • Execute a task 10 times (Counter = 0; Counter < 10; Counter++).

   • Process files with incremental names (File_1.csv, File_2.csv, etc.).

3. Foreach Loop Container

   • Process all .csv files in a folder.

   • Execute a task for each row in a SQL query result.

---

3. Example: Using a Foreach Loop to Process Multiple Files

1. Foreach Loop Container → Configured to iterate over files in a folder.

2. Inside the loop:

   • A File System Task moves each file to an archive folder.

   • A Data Flow Task loads data from the file into SQL Server.

---

4. Key Differences Between Tasks and Containers

Feature	Tasks	Containers
Purpose	Perform a single action.	Group tasks or apply looping logic.
Execution	Runs once unless inside a loop.	Can execute tasks multiple times.
Examples	Execute SQL, Data Flow, Script Task.	Sequence, For Loop, Foreach Loop.

---

Conclusion

• Tasks perform specific actions (SQL queries, file operations, etc.).

• Containers help structure workflows (sequential execution, loops, transactions).

• Combining tasks and containers allows for complex, automated ETL processes.

• 
  

For Each Loop Container in SSIS

For Each Loop Container in SSIS:

Why For Each Loop Container Task is Use ?

• For Each Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
• In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
• Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Enumerator is nothing but an iterator where an object that enables a programmer to traverse a container

SQL Server Integration Services provides the following enumerator types:

1. Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
2. Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
3. Foreach ADO Enumerator: Useful for enumerating rows in tables.
4. Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
5. Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
6. Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
7. Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.

How For Each Loop Container Task is look like ?

This is Second task in tab itself !!

Drag this out in your development plane

Features of For Each Loop Container Task

The following diagram shows a Foreach Loop container that has a File System task. The Foreach loop uses the Foreach File enumerator, and the File System task is configured to copy a file. If the folder that the enumerator specifies contains four files, the loop repeats four times and copies four files.