How to delete all files in a folder except Latest by using SSIS Package - SSIS Tutorial

Scenario:

We have a situation, where one of our process download files every day. The process runs on schedule and download multiple files.Our requirement is to load the latest file to SQL server Table. We don't care about old files, we want to delete them. How would you do that in SSIS Package?

Solution:


There are multiple ways to handle this situation, we can delete the old file and leave the latest and then can use the foreach loop to load the latest file to SQL Server Table.

In this post, I am going to do the first part. We will be using script task to delete all old files from a folder and leave the latest. To load file from a folder, you can take a look into Here.

Step 1:
Go ahead and create SSIS Package in your solution. Then create a variable called FolderPath as shown below.

How to delete all files in a folder except latest file-Create Variable for Folder Path- SSIS Tutorial




Step 2: 
Bring Script Task to Control Flow and Map the Variable FolderPath and choose the scripting language, I have selected C#.
How to delete all the files in a folder by using Scrpt Task in SSIS Package - SSIS Tutorial



Step 3:
Add System.IO namespace under Namespaces

Step 4:
Add the 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.MinValue;
            string filename="";
      //Get the lastest file name
            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime > lastModified)
                {
                    lastModified = file.LastWriteTime;
                    filename = file.Name;
                    //MessageBox.Show(filename);
                }
            }
      //Delete all old files except latest one.
            foreach (FileInfo file in files)
            {      
                   // MessageBox.Show(filename);
                    if (file.Name !=filename)
                    {
                        file.Delete();
                    }
               
            }


Save the script. You are all done.

Go ahead and provide the folder path in Value of FolderPath variable. Execute SSIS Package.It should delete all filed except latest.

Step 5:
Follow the steps  Here.to read the file and load to Table.

1 comment: