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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.