How to Avoid FTP Task Error when No File Found on FTP Server in SSIS Package- SQL Server Integration Services(SSIS) Tutorial


We need to download or remove the file from FTP Server. But let's say the file is not present. If we try to download or delete the file we will get an error as file is not available. How we can avoid this error in SSIS Package.

What we will learn this in video
  1. How to run couple of test with FTP Task to Fail if File not exists in SSIS Package
  2. Create Variables to hole remote path, File Name , Local Folder paths in SSIS Package
  3. How to create FTP Connection Manager and use in Script Task
  4. How to create Flag variable in SSIS Package and update the value in Script task depending upon the availability of file on FTP Server
  5. How to use Precedence Constraint in SSIS Package to handle the flow of Tasks
Script that you will use in Script task to Check if File exists on FTP Server or not.

Public Sub Main()

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

        RemoteDirectory = Dts.Variables("User::RemoteFolder").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

        ftp.SetWorkingDirectory(RemoteDirectory) 'Provide the Directory on which you are working on FTP Server

        ftp.GetListing(StrFolderArrary, StrFileArray) 'Get all the files and Folders List

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

        If StrFileArray Is Nothing Then

            Dts.Variables("User::Flag").Value = 0

            'If Files are there, Loop through the StrFileArray arrary and insert into table


            For Each fileName In StrFileArray

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


        End If
        ' Add your code here
        Dts.TaskResult = ScriptResults.Success
    End Sub

How to Avoid FTP Task Error when No File Found on FTP Server in SSIS Package

Related Posts / Videos on FTP Task / Script Task