Delete the oldest file in a folder with SSIS

Delete the oldest file in a folder with SSIS
Some cases the developer wants to delete the oldest or the latest file, or maybe just save the name in a variable. In this case I am deleting the oldest file

Criteria
1. Make a folder at “C:\SSIS\DeleteTheOldestFile\FolderToDeleteFrom”
2. Copy some text files in it with different “Created Dates”

Over all View


Defining some variables in the package
One of the most important part of a SSIS package are the variables, because some variables will be defined in the Configuration File(s), so that you can use the package dynamically.


1. “uVar_CurrentFileDateTime” a Defined date for the max date.
2. “uVar_CurrentFileName” The current file name
3. “uVar_FolderNameOfFilesToBeProcessed” the folder that will be looking for the oldest file to be deleted
4. “uVar_OldestFileName” The name of the oldest file.
5. “uVar_SourceFileExtension” The files extension.

Adding Object (controls)
Now we can add the objects as seen in the “Over all View”
1. FLC -- Loop through a folder and seek selected files one by one

Setting the ForLoop to find the text files by using the “uVar_SourceFileExtention” and looking into the folder that is in “uVar_FolderNameOfFilesToBeProcessed”.
By setting the Traverse Subfolders it will look into the sub folders as well.

While loop through files, save the file name and path into the above variable

2. SCR -- Find the oldest file by date and time


Now we can use the variable and a script to find each file’s “Created date” and compare it with the others, by saving the oldest file name and folder in the “uVar_OldestFileName” variable



3. fx
In this section we have to check that is they are any files to be deleted, otherwise the package will fail if no file found.


4. FSYS -- Delete the oldest file
Delete the oldest file


Testing Scenarios
Before testing make sure that you have set the variables according to the snap shot of the variables and have the “Criteria” checked.

Scenario 1:
1- Check the folder “C:\SSIS\DeleteTheOldestFile\FolderToDeleteFrom” as make sure that you have few txt files.
2- Check the Create date of each file
3- Run the package and check the folder again and see what files have been deleted.


Scenario 2:
This it with Excel files
1- Make a folder “C:\MySSIS\MainFolder\DelOldest”
2- Save the folder in “uVar_FolderNameOfFilesToBeProcessed”
3- Set the Variable “uVar_SourceFileExtension” = *.xls
4- Copy some excel files in that folder
5- Run the package
6- See what files have been deleted

Note: Try running the package multiple times.