Sync FTP Folder to Local Folder without Downloading Existing Files by using Script Task in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

A vendor put multiple files in FTP Server on designated folder for us to download. We want to build a process in which we want to sync our local folder to FTP Server folder. We want to download only the files which we don't have in our local folder. The process can run on schedule , maybe multiple times a days and download only new files after comparing with our local folder.


Solution:

SSIS provides us FTP Task that can be use to download/Update and perform other operations on FTP Server. FTP Task has it limitation and once of that we can't compare the folders and get only new files. In this video we will be using Script Task to overcome limitation of FTP Task.

What you will learn in this video.
  1. How to Create an SSIS Package from Scratch
  2. How to create Package Parameters in SSIS Package for Local Folder and Remote Folder
  3. How to use Package Parameters in Script Task
  4. How to download files from FTP Server to Local which does not exists in Local Folder
Script to Sync FTP Folder to Local Folder by using VB.net in Script Task in SSIS Package
      
Dim StrFolderArrary As String()
        Dim StrFileArray As String()
        Dim RemoteDirectory As String
        Dim LocalFolder As String
        Dim LocalFolderFiles As String()
        Dim fileName As String
     

        'Set Local Variable values by using SSIS Package variables
        'RemoteDirectory = Dts.Variables("User::RemoteFolder").Value.ToString()
        RemoteDirectory = Dts.Variables("$Package::RemoteFolder").Value.ToString()
        'LocalFolder = Dts.Variables("User::LocalFolder").Value.ToString()
        LocalFolder = Dts.Variables("$Package::LocalFolder").Value.ToString()
        LocalFolderFiles = Directory.GetFiles(LocalFolder)
  

'FTP connection manager name

Dim cm As ConnectionManager = Dts.Connections("FTPConnection") 
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,
        'Nothing to download so close the ftp connection.
        If StrFileArray Is Nothing Then
           
            ftp.Close()
'If files available , check and compare and download non existing files to Local Folder
        Else
            For Each fileName In StrFileArray
                'Check if file already exists in Local folder and FTP Server Folder
                If CType(LocalFolderFiles, IList).Contains(LocalFolder + "\" + fileName) 
Then
                    MessageBox.Show(fileName + " File already exists")
                Else
                    Dim DownloadFileNameArrary As String()
                    DownloadFileNameArrary = {RemoteDirectory + "/" + fileName}
                    'Download the file
                    ftp.ReceiveFiles(DownloadFileNameArrary, LocalFolder, False, True)
                    MessageBox.Show(fileName + " download successfully")
                End If
            Next

            ftp.Close()
        End If


How to Sync Local Folder with FTP Folder without Downloading Existing Files by using Visual Basic in Script Task in SSIS Package



Related Posts / Videos on FTP Task / Script Task

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)