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.

---

4. Renaming a File After Successful Processing

Scenario: After loading data from data_temp.csv, rename it to data_processed.csv.
Solution: Use File System Task with Rename.

Configuration:

• Operation: Rename file

• Source: C:\ETL\Staging\data_temp.csv

• Destination: C:\ETL\Staging\data_processed.csv

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

---

5. Creating a Directory if It Doesn’t Exist

Scenario: Before exporting a report, ensure the output folder exists.
Solution: Use File System Task with Create Directory.

Configuration:

• Operation: Create directory

• Folder Path: C:\Reports\2024\May

• Usage: Before an Export Task (e.g., Data Flow or Execute Process Task).

---

File System Task Configuration Options

Setting	Description
Operation	Copy, Move, Delete, Rename, Create Directory, Set Attributes
Source Connection	File/Folder to be processed (can be a variable)
Destination Connection	Target path (for Copy, Move, Rename)
OverwriteDestination	If True, replaces existing files
Name/Path from Variable	Dynamic file paths using SSIS variables
IsSourcePathVariable	Use a variable for source path (e.g., @[User::SourceFile])

---

Best Practices

✔ Use variables for dynamic paths (e.g., @[User::SourceFolder] + "file.csv").
✔ Check if a file exists before moving/deleting (use a Script Task or Expression).
✔ Handle errors (e.g., if a file is locked, use Error Handling in SSIS).
✔ Use UNC paths (e.g., \\Server\Share\file.txt) for network files.
✔ Avoid hardcoding paths – use project/package parameters.

---

Conclusion

The File System Task is essential for:

• File management (Copy, Move, Delete, Rename)

• Folder operations (Create, Delete)

• Automating file workflows in ETL processes