I have always know that that SSIS has an FTP control Flow task, but I have never tried to use.  Turn out that the task is fairly limited and can be a pain to work with.  I am attempting to get a list of all files, in a folder, on an FTP server.   The FTP Task is designed to send and receive files and folders via FTP.  The other problem that I was having is I could not save the FTP password with a configuration.  The connection string for an FTP connection only includes the site and port, not the user or password.

I did the following to get my list of files

1. Create an FTP Connection manager.  I named mine connFTP.  You do not have to file in all the info here.  We will overwrite it in a Script Task.  You will need to put something in the server name.  
2. Create package variables
   1. One for each of the following  FTP server, port, user name and password. 
   2. A variable for the path to the files, if the files are not in the FTP root directory, vFTPFileDir.  In my case this looked like “/user name/working folder”
   3. A variable to store the file list, of type Object,   vFTPFileList.
3. Create a script Task (this example is in VB)

 

'1st we must set up the connection to the FTP server

Dim cmFTP As ConnectionManager = Dts.Connections("connFTP")

'Set the FTP connection properties like username & password

cmFTP.Properties("ServerName").SetValue(cmFTP, Dts.Variables("vFTPConnectionString").Value

cmFTP.Properties("ServerUserName").SetValue(cmFTP, Dts.Variables("vFTPUserName").Value)cmFTP.Properties("ServerPassword").SetValue(cmFTP, Dts.Variables("vFTPPassword").Value)cmFTP.Properties("ServerPort").SetValue(cmFTP,  Dts.Variables("vFTPPort").Value)

'Next we will connect to the FTP Server

'create the FTP object that sends the files and pass it the connection created above.

Dim FtpCC As FtpClientConnection = New FtpClientConnection(cmFTP.AcquireConnection(Nothing))

'Connects to the ftp server

 FtpCC .Connect()

'Navigates to the directory in the FTP site

 ftpVentive.SetWorkingDirectory(Dts.Variables("vFTPFileDir").Value)

'Finally  we want to store the list of files to the variable

'Declare the string arrays

Dim saFileNames() As String = Nothing

Dim saFolderNames() As String = Nothing

'Store a list of folders and files into string arrays

FtpCC .GetListing(saFolderNames, saFileNames)

'Set the variable to the file list

 Dts.Variables("vFTPFileList").Value = saFileNames

'This part is optional to loop through each file

 If Not saFileName Is Nothing Then

For Each sFileName As String In saFileNames

'Do Something

Next

End If

'Close the FTP connection

ftpVentive.Close()

Dts.TaskResult = ScriptResults.Success

 

4. Add a Foreach Loop Container
   1. Set the enumerator to Foreach From Variable Enumerator
   2. Set the variable to vFTPFileList

There you have it.  You can now store a list of all the files in an FTP directory and loop through it in a Script Task or in a Foreach Task.