SSIS – Delete all files except for the most recent one

Posted by

Quite often one or more sources for a data warehouse consist of flat files. Most of the times these files are delivered as a zip file with a date in the file name, for example

Currently I work at a project that does a full load into the data warehouse every night. A zip file with some flat files in it is dropped in a directory on a daily basis. Sometimes there are multiple zip files in the directory, this can happen because the ETL failed or somebody puts a new zip file in the directory manually. Because the ETL isn’t incremental only the most recent file needs to be loaded. To implement this I used the simple code below; it checks which file is the most recent and deletes all other files.

Usage is quite simple, just copy/paste the code in your script task and create two SSIS variables:

  • SourceFolder (type String): The folder that contains the (zip) files
  • DateInFilename (type Boolean): A flag, set it to True if your filename ends with the date YYYYMMDD, set it to false if creation date of the files should be used

Note: In a previous blog post I wrote about unzipping zip files within SSIS, you might also find this useful: SSIS – Unpack a ZIP file with the Script Task

Public Sub Main()

    'Use this piece of code to loop through a set of files in a directory
    'and delete all files except for the most recent one based on a date in the filename.

    'File name example:

    Dim rootDirectory As New DirectoryInfo(Dts.Variables("SourceFolder").Value.ToString) 'Set the directory in SSIS variable SourceFolder. For example: D:\Export\
    Dim mostRecentFile As String = ""
    Dim currentFileDate As Integer
    Dim mostRecentFileDate As Integer
    Dim currentFileCreationDate As Date
    Dim mostRecentFileCreationDate As Date

    Dim dateInFilename As Boolean = Dts.Variables("DateInFilename").Value 'If your filename ends with the date YYYYMMDD set SSIS variable DateInFilename to True. If not set to False.

    If dateInFilename Then

        'Check which file is the most recent
        For Each fi As FileInfo In rootDirectory.GetFiles("*.zip")

            currentFileDate = CInt(Left(Right(fi.Name, 12), 8)) 'Get date from current filename (based on a file that ends with:

            If currentFileDate > mostRecentFileDate Then

                mostRecentFileDate = currentFileDate
                mostRecentFile = fi.Name

            End If


    Else 'Date is not in filename, use creation date

        'Check which file is the most recent
        For Each fi As FileInfo In rootDirectory.GetFiles("*.zip")

            currentFileCreationDate = fi.CreationTime 'Get creation date of current file

            If currentFileCreationDate > mostRecentFileCreationDate Then

                mostRecentFileCreationDate = currentFileCreationDate
                mostRecentFile = fi.Name

            End If


    End If

    'Delete all files except the most recent one
    For Each fi As FileInfo In rootDirectory.GetFiles("*.zip")

        If fi.Name <> mostRecentFile Then

            File.Delete(rootDirectory.ToString + "\" + fi.Name)

        End If


    Dts.TaskResult = ScriptResults.Success
End Sub


  1. Great post, Jorg – I can’t tell you how often this question is asked on the forums.  Now I’ll just have to remember where to find this post so I can refer people to it!
    Can you edit that sample above to use FileInfo to get the actual modification date for your comparison?  Even though most people inquire about doing it exactly the way you have (the date is encoded in the file name) – they also tend to ask the same question when it’s not.


  2. @Todd: Thanks, I assume this post can help quite some people and that why I wrote it 🙂 I have changed the code so you can choose between using a date in the filename or the creation date. Thanks for the suggestion.
    @Mike C: I haven’t experienced any problems with FileInfo over the network here so I haven’t changed the code. Thanks for pointing me to FileInfo .CreationTime!


  3. I would suggest you use ModificationTime on the file, as creation time is when the file arrives on your filesystem, not when it was last written.
    If you copy the file from a server to your machine, the Creation Time is when you copied the file, Not the CreationDate you actually expect.
    Modificationtime remains consistent in both scenarios.


  4. Hi Jorg,
    You might note that it is a Microsoft Visual Basic (2008) script and that the System.IO namespace is required, e.g. “Imports System.IO”
    Great post!


  5. Very useful thanks.  Needed some code to find the latest csv file in a folder a log it’s name, modification\created date etc.  This helped me on my way.  Excellent post.


  6. Very good. Just what I needed. 🙂
    As per a previous post, the only thing missing was.
    Imports System.IO
    Also very easy to modify to delete all .JPG in directory. which is another common issue if retrieving attachments from emails,
    Thanks again.


  7. Hi Jorg,
    I used your code to keep the recent file and delete the rest of the files. I am getting a weird situation when i used your code where “Get creation date of current file”, I have 4 files in the remote folder  where I downloaded these files and move it to local folder and then in local folder i want to delete all files except the recent one
    when i ran the script task for the first time it is keeping the file with date 2/9/2015  and deleting the rest and when i ran for the second time it is keeping 2/12/2015 and deleting the rest. is there any bug  in the code? i tried so many times but not able to find out why it is behaving like that. can you help me out??


  8. Sorry got it,
    I was using CreationTime in the code .. instead of Creationtime i need to use LastWriteTime. Then it will work.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s