Sync Local Folder to FTP Server Folder without Upload Existing Files by SSIS Package - SQL Server Integration Services(SSIS) Tutorial


We have multiple business users who put the files in our Local Folder so we can upload them to FTP Server Folder. There are changes they will put the same file again after making some corrections, but we don't want to upload any file that has been already loaded. In simple we want to upload only the files from Local Folder to FTP Server which are not uploaded. The package can run every hour and compare the files on Local Folder and FTP Server and then upload newly arrived files to Local Folder.


This is kind of complex situation, SSIS does provide us FTP Task that can be used to upload the files but it does not provide option to ignore the upload if file already exists. It does have the capability of overwriting the file in destination ( Remote) but we don't want to overwrite existing files.

For this requirement, we will be using Script task.

Things you will learn in this video
How to Create an SSIS Package from basics
How to create variables in SSIS Package for Local Path, Remote Path,Filename
How to use For-each Loop Container to loop through files from Local Folder
How to check if files exits on FTP Server or Not, If not then Upload it
How to use FTP Connection Manager in Script Task

Script to Sync Local Folder to FTP Folder by using VB.Net in SSIS Package

        Dim StrFolderArrary As String()
        Dim StrFileArray As String()
        Dim RemoteDirectory As String
        Dim LocalFolder As String
        Dim LocalFileNames As String()
        Dim ForEachFileName As String

        'Set Local Variable values by using SSIS Package variables
        RemoteDirectory = Dts.Variables("User::RemoteFolder").Value.ToString()
        LocalFolder = Dts.Variables("User::LocalFolder").Value.ToString()
        ForEachFileName = Dts.Variables("User::FileName").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

        '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 upload the file and then close the connection.

        If StrFileArray Is Nothing Then
            LocalFileNames = {LocalFolder + "/" + ForEachFileName}
            ftp.SendFiles(LocalFileNames, RemoteDirectory, False, True)

'If file is not there then upload the file , if there exit the ftp server

            If CType(StrFileArray, IList).Contains(ForEachFileName) Then
                MessageBox.Show("File already exists")

                LocalFileNames = {LocalFolder + "/" + ForEachFileName}
                ftp.SendFiles(LocalFileNames, RemoteDirectory, False, True)
                MessageBox.Show("Upload successfully")
            End If
        End If

How to Sync Local Folder to FTP Server Folder by using VB.Net in Scrip Task in SSIS Package

Related Posts / Videos on FTP Task / Script Task

1 comment:

  1. I really appreciate what you have on this course. Can you please do a video how to download files from SFTP using script task and WinSCP or any other method. Thanks!