Welcome To TechBrothersIT: 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.

How to Create an SSIS Package from Scratch
How to create Package Parameters in SSIS Package for Local Folder and Remote Folder
How to use Package Parameters in Script Task
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

2 comments:

UnknownAugust 21, 2020 at 11:47 AM
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

Label

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

Scenario:

Solution:

2 comments: