SSIS - Delete files older than retention period from folder with parameter using Script Task in SQL Server

pprajapati — Fri, 10 Feb 2012 14:03:00 GMT

After using some of the ways to delete the files from the particular folder like Delete file using Script Task and File System Task in SSIS. We have seen such methods and used logic to get the files from the loop container and process to delete them and same thing applied for the scripts as well. we also looked for the files deletion which are older than some Retention period with Script Task also.

In earlier posts we have not used any parameters in the scripts and directly applied folder path and retention period values there. But here i would like to use parameters for the folder path and retention period and pass through the scripts and delete them as per condition.

1. Let check the files from the target folder.

2. Create parameters and set the values. Here it is going to be delete the files which are older then 3 days from the E:\ImagesBackup folder in this case.

3. Drag and drop File System Task

4. Put the parameters as ReadOnlyVariables

5. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not. Here you can see the parameters used in the script.

Please note here we need to import system.IO namespace.

6. Turn on final step and run package. Files older than specified retention period get deleted.

You can use the script below for the same as mentioned in above image,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");

foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

SSIS - Delete files older than retention period from specified folder using Script Task in SQL Server

pprajapati — Sat, 04 Feb 2012 13:18:00 GMT

Earlier we have seen for the Delete file using Script Task and files deletion using File System Task in SSIS. We have directly wrote the logic to get the files and delete from specified folder in the script for the Script Task. For File System Task we have Used Foreach Loop Container and variables to hold file names which are passed from earlier stage one by one and then finally used with File System Task to delete it.

Here we have same but not to delete all the files inside passed folder but get deleted files which are older than specified days. This is nothing but minor change in script where we pass the folder path and retention period. Lets start the steps as following,

1. Let check the files from the target folder.

2. Drag and drop Script Task.

3. Open script editor from the properties.

4. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not.

Please note here we need to import system.IO namespace.

5. Turn on final step and run package. Files older than specified retention period get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = 3;
string directoryPath = @"E:\ImagesBackup";
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.png");
     foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Search results matching tag 'retention'

SSIS - Delete files older than retention period from folder with parameter using Script Task in SQL Server

SSIS - Delete files older than retention period from specified folder using Script Task in SQL Server