How to delete oldest files from a folder using SSIS

[daniel]

Introduction

Managing disk space efficiently is crucial when working with SQL Server Integration Services (SSIS). Large volumes of temporary files, logs, and backups can quickly consume storage, making automated file cleanup a necessity. In this article, we will demonstrate how to remove the oldest files in a folder using the Advanced File System Task from the ZappySys SSIS PowerPack. This method simplifies file management and ensures outdated files are deleted based on specific criteria.

Prerequisites

• Download and install the ZappySys SSIS PowerPack

Method 1: Using a Foreach Loop Container

This method retrieves a list of the oldest files and deletes them one by one using a loop.

Step 1: Configure the Advanced File System Task

1. Drag and drop the Advanced File System Task into the Control Flow.

2. Open the task and configure the following settings:

   • Set Action to Get File List as ADO.net DataTable.
   • Specify the Folder Path (wildcards can be used).
   • Select an Object Variable to store the result.
   

3. Go to the Filter and Sorting tab:

   • Enable Sorting.
   • Set Sort By to File Creation DateTime (or Last Modified, if preferred).
   • Enter the number of files to retrieve in the Top field.
   

Step 2: Use a Foreach Loop Container to Process Files

1. Drag and drop a Foreach Loop Container into the Control Flow.

2. Open its Collection tab:

   • Set Enumerator to Foreach ADO Enumerator.
   • Select the Object Variable created in Step 1.
   

3. In the Variable Mapping tab, map a String variable to store the file path from the retrieved list.

   

Step 3: Delete the Files

1. Drag and drop another Advanced File System Task inside the Foreach Loop Container.

2. Configure it to:

   • Delete File(s).
   • Use the Variable (mapped in Step 2) as the file path.
   

3. Execute the package; it will delete the oldest files iteratively.

   

Optional: Log Deleted Files

For audit purposes, you can add a Logging Task inside the Foreach Loop Container to store details of deleted files.

---

Method 2: Using the Delete Option with Filters

This method deletes files directly based on a predefined filter without using a loop.

Step 1: Configure the Advanced File System Task

1. Drag and drop an Advanced File System Task.

2. Set Action to Delete File(s).

3. Specify the Folder Path where the files are stored.

   

Step 2: Apply Filtering

1. Open the Filter and Sorting tab.

2. Enable Sorting and select:

   • Sort By: File Creation DateTime (or Last-Modified).
   • Top N: The number of oldest files to delete.
   

Step 3: Execute the Package

• Run the package, and the selected oldest files will be deleted automatically.

---

Conclusion

Using the Advanced File System Task, you can efficiently automate the removal of outdated files in SSIS. The Foreach Loop Container method offers flexibility for logging and selective deletions, while the direct Delete method is ideal for bulk cleanup. Both approaches help maintain disk space and keep your storage organized.

For more advanced SSIS file operations, explore the ZappySys SSIS PowerPack.

References

• Advanced File System Task
• Advanced File System Task – Documention
• Blog articles
• SSIS PowerPack