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.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)
            End If
        Next fileName


        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.

3 thoughts on “How To Get a List of Files from a FTP Server in SSIS

  • Wednesday April 13th, 2011 at 10:24

    Thank you so much for this script. It works great for my project. I was actually downloading everything even files that I didnt need and then looping the files. But now I only download what I want with the STARTS WITH command. Thanks again.

  • Wednesday August 22nd, 2012 at 23:40

    SQLDMO.QueryResults qRs = oServer.EnumAvailableMedia
    SQLDMO.QueryResults qRs = oServer.EnumDirectories(currentPath);

    exec master..xp_cmdshell ‘dir “D:\Program Files”‘
    exec master..xp_subdirs ‘d:\program files’
    exec master..xp_dirtree ‘d:\program files’

  • Sunday July 21st, 2013 at 01:19

    Thanks, Jack. Works great.


Leave a Reply

Your email address will not be published. Required fields are marked *