I have a folder into which logs are produced every day.
I want to create an SSIS package that will run every day and delete files older than 90 days (based on created date). I have worked out a solution using the script task (C#) and wanted to know whether there is a more straightforward way (for example - an SSIS task) to achieve this?
I have worked out a solution using the script task (C#) and wanted to know whether there is a more straightforward way (for example - an SSIS task) to achieve this?
There is an SSIS Task called Maintenance Cleanup Task that removes the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder based on their creation date.
This task is a SQL Server Agent Maintenance plan task that is also available in SSIS.
Maintenance Cleanup Task uses the master.dbo.xp_delete_file system stored procedure to delete the files that meet the criteria specified in the Task's configuration.
It is not recommended to use Maintenance Cleanup Task since this task is not developed to perform a general file cleanup task. It is used to perform a specific SQL Server Agent operation, even if users can specify the file extension they need to clean. Besides, only one level of subfolders is supported in the cleanup task.
Unfortunately, there is no other SSIS task that can delete files based on their creation or modified date. You should implement your logic using a Script Task component as you already did.
Besides, a script task (.NET framework) provides lots of features that are not available in the Maintenance cleanup task such as traversing subfolders, deleting files based on other dates stored in the metadata (Last Modified Date), deleting multiple file extension, excluding several extensions...
You can write all the logic within the Script Task (Loop over files, delete them). Or you can use it to list all files, and use a Foreach Loop Container with a File System Task to delete them as explained in the following example: SQL Server Integration Services package to delete files from a Network or Local path based on date.
