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

1 comment:

  1. Hi!
    I took the liberty and changed your main() procedure by adding to it a recursive procedure of synchronization folders / subfolders. In this case we can always completely synchronize all content of the remote folder to the local folder. Your work pushed me to do this. Thank you! See the code below:

    Public Sub Main()
    ' Declare local variables
    Dim RemoteFolder As String
    Dim LocalFolder As String

    ' Set local variables from the package parameters
    RemoteFolder = Dts.Variables("$Package::RemoteFolder").Value.ToString()
    LocalFolder = Dts.Variables("$Package::LocalFolder").Value.ToString()

    'Connect to the FTP
    Dim conn As ConnectionManager = Dts.Connections("FTPConnection") 'FTP connection manager name
    Dim ftp As FtpClientConnection = New FtpClientConnection(conn.AcquireConnection(Nothing))

    ftp.Connect() 'Connect to the FTP

    DownloadFTPFolderContent(ftp, RemoteFolder, LocalFolder)

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

    Public Sub DownloadFTPFolderContent(ftp As FtpClientConnection, RemoteFolder As String, LocalFolder As String)
    Dim StrFolderArray As String()
    Dim StrFilesArray As String()
    Dim LocalFolderFiles As String()
    Dim SubFolderName As String
    Dim fileName As String
    Dim DownloadFileName As String()

    ' Provide ftp working directory
    ftp.SetWorkingDirectory(RemoteFolder)

    ' Get file list of Local folder
    LocalFolderFiles = Directory.GetFiles(LocalFolder)

    ' Get list of FTP subfolders and files
    ftp.GetListing(StrFolderArray, StrFilesArray)

    ' Synchronize content of the remote folder to the local folder
    ' If the remote folder have subfolders
    If Not (StrFolderArray Is Nothing) Then
    For Each SubFolderName In StrFolderArray
    ' Create the subfolder in the local folder if it's not exists
    If Not Directory.Exists(LocalFolder + "\" + SubFolderName) Then
    My.Computer.FileSystem.CreateDirectory(LocalFolder + "\" + SubFolderName)
    End If

    ' Download content of the subfolder
    DownloadFTPFolderContent(ftp, RemoteFolder + "/" + SubFolderName, LocalFolder + "\" + SubFolderName)
    Next
    End If

    ' Download files from the remote (sub)folder to the local (sub)folder
    If Not (StrFilesArray Is Nothing) Then
    For Each fileName In StrFilesArray
    ' Download the file if it's not exists
    If Not (CType(LocalFolderFiles, IList).Contains(LocalFolder + "\" + fileName)) Then
    DownloadFileName = {RemoteFolder + "/" + fileName}
    ' Download the file
    ftp.ReceiveFiles(DownloadFileName, LocalFolder, False, False)
    End If
    Next
    End If
    End Sub

    ReplyDelete