Introduction
When working with SSIS (SQL Server Integration Services), managing file retention on an SFTP server is essential for optimizing storage and maintaining system performance. Deleting old files helps prevent clutter and ensures that only relevant data remains accessible. This article will guide you through configuring an SSIS package to delete old files from an SFTP server using the SFTP Task in ZappySys SSIS PowerPack.
Prerequisites
- Download and install the ZappySys SSIS PowerPack.
Steps
-
Drag and drop the SFTP Task into the control flow.
-
Open the task and create a new connection:
- Select the protocol you need
- Enter your hostname and port number
- Enter your username and password
- Select the logon type
- Explore additional options in the other tabs if needed
- Test the connection and click OK to save the configuration
-
Configure the following settings inside the SFTP task:
- Set the action to Delete FTP file(s)
- Select the connection you created
- Specify the folder path (wildcards can be used)
-
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 delete in the top field
- Alternatively, use a where condition to filter files based on a specific date
-
Run the SSIS package.
-
Verify that the old files have been successfully deleted.
Conclusion
Automating file deletion from an SFTP server using SSIS helps maintain a clean and efficient storage environment. The SFTP Task allows you to schedule and execute file cleanup based on specific criteria, such as file age or modification date. This approach ensures optimal SFTP server performance and improves data management in your ETL workflows. For added automation, consider scheduling the task with SQL Server Agent.
For more advanced SSIS file operations, explore the ZappySys SSIS PowerPack.
References
Contact us
If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.