Loop through FTP Files On Remote Folder and Delete OLD Files – SSIS – .NET Code

Few days ago, I encounter a request which asks for delete files on remote ftp folder which are older than 10 days with SSIS tasks. Suppose date of each file is embeded in the file name like this: zzz_2010-10-25 03-00-22.txt

As you may know , using Foreach loop with configuring enumerator as file enumerator will not work on remote ftp folder. a work around can be this: copy all remote files to a local directory and then find file names which are old, and then in a foreach loop delete these files with ftp task. This is a doable solution BUT copying all files from remote ftp to local directory is not wise solution because there maybe thousand files on remote folder and copying them to local directory will take long time.

So there is a Good Solution for this problem which needs scripting, we can use the Powerful Script Task for this purpose with few lines of .NET code to loop through remote files and find old files and delete them.

This is the script I used to handle this problem with the help of System.Net.WebRequest :

Public Sub Main()

        Try
            Dim request As System.Net.FtpWebRequest
            request = System.Net.WebRequest.Create("ftp://remoteurl.com/remotefolder/")
            request.Method = System.Net.WebRequestMethods.Ftp.ListDirectoryDetails

            request.Credentials = New System.Net.NetworkCredential("username", "password")

            Dim response As System.Net.FtpWebResponse
            response = request.GetResponse()

            Dim responseStream As System.IO.Stream
            responseStream = response.GetResponseStream()

            Dim reader As System.IO.StreamReader = New System.IO.StreamReader(responseStream)

            While Not reader.EndOfStream
                Dim strTemp As String = reader.ReadLine
                Dim fileName = strTemp.Substring(strTemp.IndexOf("_") – 3)
                Dim theDate As DateTime = DateTime.ParseExact(fileName.Substring(4, 10), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)
                If (theDate.Date < DateTime.Now.AddDays(-10).Date) Then
                    request = System.Net.WebRequest.Create("ftp://remoteurl.com/remotefolder/"+fileName)
                    request.Method = System.Net.WebRequestMethods.Ftp.DeleteFile
                    request.Credentials = New System.Net.NetworkCredential("username", "password")

                    response = request.GetResponse()
                End If

            End While


            reader.Close()
            response.Close()
        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
        End Try

        Dts.TaskResult = ScriptResults.Success
    End Sub

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.