How To Get a List of Files from a FTP Server in SSIS

In SSIS, the FTP task provides the following operations:
Send files
Receive files
Create local directory
Create remote directory
Remove local directory
Remove remote directory
Delete local files
Delete remote files

As you can see, there is no operation to retrieve a list of files. 

The Foreach Loop Container doesn’t work for FTP, it only works on local folder.

You can use the Script task to do this.  Here is a sample SSIS package to retrieve Demo*.xls from the FTP server. Then download all files to a local drive and remove only the remote files that we’ve transferred.

The SetWorkingDirectory can be use to specify the folder on the FTP server to query for the list of files.

    Public Sub Main()
        Dim result As Integer
        Dim conMan As ConnectionManager
        Dim ftp_client As FtpClientConnection
        Dim sFolderNames() As String
        Dim sFileNames() As String
        Dim sFileName(0) As String

        conMan = Dts.Connections("MyFTP")
        ftp_client = New FtpClientConnection(conMan.AcquireConnection(Nothing))

        ftp_client.Connect()
        ftp_client.SetWorkingDirectory("incoming")
        ftp_client.GetListing(sFolderNames, sFileNames)

        For Each fileName As String In sFileNames
            sFileName(0) = fileName
            If fileName.EndsWith("xls", StringComparison.OrdinalIgnoreCase) And fileName.StartsWith("Demo", StringComparison.OrdinalIgnoreCase) Then
                ftp_client.ReceiveFiles(sFileName, "D:\Test\ftp_data\", True, False)
                ftp_client.DeleteFiles(sFileName)
            End If
        Next fileName

        ftp_client.Close()

        Dts.TaskResult = Dts.Results.Success
    End Sub

Once you have the file on local folder, you can use Foreach Loop Container to process these files.