How to move old files and compress them using SSIS (archiving files)

[daniel]

Introduction

When working with SSIS (SQL Server Integration Services), managing old files efficiently is a common requirement in ETL processes. One of the best ways to optimize storage and ensure historical data remains accessible is by moving old files to an archive location and compressing them. This article will guide you through setting up an SSIS package to move and compress old files using the Advanced File System Task and Compression Task, which are part of the ZappySys SSIS PowerPack.

Prerequisites

• Download and install the ZappySys SSIS PowerPack.

Steps

Step 1: Move old files using 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 Move file(s).
   • Specify the Source Folder Path (wildcards can be used).
   • Specify the Destination Path to store the moved files.
   • Select the Overwrite option based on your requirements.
     
     

3. Navigate to the Filter and Sorting tab:

   • Enable Sorting.
   • Set Sort By to File Creation DateTime (or Last Modified, if preferred).
   • Specify the number of files to retrieve in the Top field.
   • Alternatively, use a Where condition to filter files based on a specific date.
     
     

Step 2: Compress moved files using Compression Task

1. Drag and drop the Compression Task and connect it to the Advanced File System Task.
2. Open the task and configure the following settings:
   • Set the Action to compress all files into one archive.
   • Select the compression format (Zip or GZip).
   • Use the destination folder from the previous step with a wildcard to include all moved files.
   • Specify a target folder to create the compressed file with the appropriate extension (e.g., .zip or .gz).
     
     

Step 3: Execute and verify results

1. Run the SSIS package.
2. Verify that the old files have been successfully moved to the archive folder and compressed.
   
   

Conclusion

Implementing this SSIS solution can efficiently automate file management, freeing up storage space while maintaining easy access to historical data. The Advanced File System Task simplifies moving old files based on date filters, and the Compression Task ensures they are stored in a compact format. This approach is ideal for archiving large volumes of data and optimizing ETL processes. For further automation, consider scheduling the SSIS package to run periodically using SQL Server Agent.

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

References

• Advanced File System Task
• Advanced File System Task – Documentation
• Compression Task
• Compression Task – Documentation
• Blog articles on Advanced File System Task
• Blog articles on Compression Task
• SSIS PowerPack

Contact us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.