How to Download a File from FTP Site and Delete after Download in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

In this video we will learn how to download a file from FTP Server Folder to Local Folder and then delete from the FTP Server Folder.

Items we will learn this video
  1. How to create SSIS Package from Scratch
  2. How to Create FTP Connection
  3. How to create variables to save Local Path, Remote Path and FileName
  4. How to use variables in Script Task to Download the file and then Delete after download if file exists
  5. How to use FTP Connection in Script Task in SSIS Package



VB.Net Script to download File from FTP Server and then Delete

Public Sub Main()

        Dim StrFolderArrary As String()
        Dim StrFileArray As String()
        Dim fileName As String
        Dim RemoteDirectory As String
        Dim LocalFolderPath As String

        'Set Local Variable values by using SSIS Package variables
        RemoteDirectory = Dts.Variables("User::RemoteDirectory").Value.ToString()
        LocalFolderPath = Dts.Variables("User::LocalFolder").Value.ToString()
        Dim cm As ConnectionManager = Dts.Connections("FTPConnection") 'FTP connection manager name


        Dim ftp As FtpClientConnection =
 New FtpClientConnection(cm.AcquireConnection(Nothing))

        ftp.Connect() 'Connecting to FTP Server

        'Provide the Directory on which you are working on FTP Server


        ftp.SetWorkingDirectory(RemoteDirectory)

        'Get all the files and Folders List


        ftp.GetListing(StrFolderArrary, StrFileArray)

'If there is no file in the folder, strFile Arry will contain nothing, so close the connection.

        If StrFileArray Is Nothing Then
            ftp.Close()

'If Files are there, Loop through the StrFileArray arrary and download the file 
'and then delete

        Else
            For Each fileName In StrFileArray

               'Check if require file is there

If fileName = Dts.Variables("User::FileName").Value.ToString() Then

                   Dim DownloadFileNameArrary As String()
                    DownloadFileNameArrary = {RemoteDirectory + "/" + fileName}

                    'Download the file

  ftp.ReceiveFiles(DownloadFileNameArrary, LocalFolderPath, True, True)

                    'Delete the file

                    ftp.DeleteFiles(DownloadFileNameArrary)

                End If
            Next
            ftp.Close()
        End If
        ' Add your code here
       '
       Dts.TaskResult = ScriptResults.Success
    End Sub



How to Download a File from FTP Server and then Delete It by using Script Task in SSIS Package




Related Posts / Videos on FTP Task / Script Task

19 comments:

  1. Thanks for your script,
    Here is one issue how we can make script or variable to pick only today's file. I saw your video for per day file and it work for static type of variable . How we can make dynamics when we specify folder and want that folder pick automatically.

    Another issue when I deploy the package to SSIS I found variables created does not move there. Any idea

    ReplyDelete
  2. Much the same as anything on Earth present day society has an equalization. So on one hand we have a dynamic electronic network and then again we have armed force of web lawbreakers whose both work and fun comprises of alleged wholesale fraud. 먹튀검증사이트

    ReplyDelete
  3. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. photographe Strasbourg

    ReplyDelete
  4. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post. sviluppo siti web Milano

    ReplyDelete
  5. Thankyou for this wondrous post, I am glad I observed this website on yahoo. sites like fiverr com

    ReplyDelete
  6. It has made information transfer so natural that even a layman can transfer huge files in a brief timeframe. file transfer

    ReplyDelete
  7. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. WordPress Developer Brisbane

    ReplyDelete
  8. I haven’t any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.
    quenza software

    ReplyDelete
  9. Most importantly among these is a capacity to comprehend the complex logical thoughts secured by the actual licenses. TutuApp web

    ReplyDelete
  10. Regardless of whether in helpful commitment or willed organizations, the guideline of equity neglects to work definitely due to the predominance disorder that holds the pioneer and the drove.TutuApp web

    ReplyDelete
  11. Thank you for your submit. I will definitely comeback. https://tipandroid.com/

    ReplyDelete
  12. I haven’t any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.
    คลินิกความงาม

    ReplyDelete
  13. I am hoping the same best effort from you in the future as well. In fact your creative writing skills has inspired me.
    메이저놀이터

    ReplyDelete
  14. Great post! I am actually getting ready to across this information, is very helpful my friend. Also great blog here with all of the valuable information you have. Keep up the good work you are doing here. 온라인포커

    ReplyDelete
  15. Your information was very useful to me. That's exactly what I've been looking for 카지노사이트추천!

    ReplyDelete
  16. Nice post. I was checking constantly this blog and I’m impressed! Extremely useful info specially the last part I care for such information a lot. I was seeking this certain info for a long time. Thank you and good luck. 먹튀사이트

    ReplyDelete
  17. Cool stuff you have got and you keep update all of us. แทงบอล i99pro

    ReplyDelete
  18. Impressive web site, Distinguished feedback that I can tackle. Im moving forward and may apply to my current job as a pet sitter, which is very enjoyable, but I need to additional expand. Regards. 안전놀이터

    ReplyDelete
  19. Great post, you have pointed out some fantastic points , I likewise think this s a very wonderful website. 안전놀이터

    ReplyDelete