How to Delete all the files from a folder expect the oldest file in SSIS Package - SSIS Tutorial


You are working as ETL Developer. You got this requirement where you need to delete all the files from a folder except the oldest. How would you do that?


We can use Script task to handle this requirement. 

Step 1:

Create new SSIS Package and inside SSIS Package, create variable for folder path called FolderPath. I always like to create variables so we can use them in configuration and if we need to change the value in different environments such as QA,UAT and Production. It is very easy to do by using configuration instead of using multiple copies of SSIS Package.

How to delete all files in a folder by using Script Task in SSIS Package except oldest file-SSIS Tutorial

Step 2:
Bring Script task to Control Flow pane in SSIS Package and open by double clicking. Map the variable FolderPath to use in Script Task also choose the scripting language. I have selected C#.

SSIS - Delete all files from a folder except the oldest one - Script Task- SSIS Tutorial

Step 3:
Open Script Task and add System.IO namespace under NameSpace Region as shown below.

How to add System.IO namespace to NameSpace Region in Script Task to Delete Files

Step 4:
Add below code under Public Void Main() {

// TODO: Add your code here
            var directory = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());
            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MaxValue;
            string filename="";
      //Get the oldest file 
            foreach (FileInfo file in files)
                 if (file.LastWriteTime < lastModified)
                    lastModified = file.LastWriteTime;
                    filename = file.Name;
      //Delete all files except oldest one.
            foreach (FileInfo file in files)
                    if (file.Name !=filename)

Step 5:
Save your folder path in value of FolderPath variable. Run the SSIS Package. It should delete all the files in a folder except the oldest one. 

No comments:

Post a Comment