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

Scenario:

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

            MessageBox.Show(Dts.Variables("User::Flag").Value.ToString())
            ftp.Close()
            Dts.Variables("User::Flag").Value = 0
          

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

        Else



            For Each fileName In StrFileArray

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

            ftp.Close()

        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

No comments:

Post a Comment