I want to loop through all files in a FTP folder and process them. I need a Foreach ftp file enumerator, but the Foreach Loop component only loops through local files.
Solution
There are a couple of options. You could use a Script Task, a wildcard in the RemotePath Property of the FTP Task or a Third Party component to download all files to a certain folder and loop through those files with a Foreach Loop file enumerator. This is the most common approach.
This example uses a Script Task to get a list of ftp files and uses a Foreach From Variable Enumerator to loop through those files.
Update: There is now a custom Foreach FTP File Enumerator available.
1) Variables
We need a couple of variables to keep things flexible:
- FtpWorkingDirectory: String variable with the remote folder path. Example: /root/data/
- DownloadDirectory: String variable that contains the path of the download folder: Example: d:\SourceFiles\
- FtpFileURL: String variable used in the foreach loop to store the remote filename in. Example: 01-01-2011.csv
- FtpFileList: Object variable to store the remote file list in.
 |
| Variables to keep things flexible |
2) Ftp Connection Manager
Create a FTP Connection Manager by right clicking in the Connection Manager pane and select New Connection... In the new window select FTP and click Add. After that fill in the FTP Connection Manager Editor. Test the connection manager and rename it to myFtpServer (this name is used in the Script Task).
 |
| New FTP connection |
 |
| After closing you can rename it. |
3) Script Task
Add a Script Task to the Control Flow and give it a suitable name. Edit the Script Task and add variable FtpWorkingDirectory as ReadOnly and FtpFileList as ReadWrite.
 |
| Script Task to get list of files |
4) The Script
Edit the script and copy the following code.
// #C Code
using System;
using System.Collections; // Added
using System.Data;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_f20cc76ba3bc47849f70c6cbfd4701dc.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
try
{
// Get the ftp connection from the Connection Managers collection
ConnectionManager ftpServer = Dts.Connections["myFtpServer"];
// Create a FTP connection object and us the credentials of connection manager
FtpClientConnection myFtpConnection = new FtpClientConnection(ftpServer.AcquireConnection(null));
// Open the connection
myFtpConnection.Connect();
// Set work folder with the value of the variable
myFtpConnection.SetWorkingDirectory(Dts.Variables["FtpWorkingDirectory"].Value.ToString());
// Create StringArrays for filenames and folders
// The folderNames aren't used, but is mandatory
// for the next method.
String[] fileNames;
String[] folderNames;
// Get a directory listing and fill the StringArray variables
myFtpConnection.GetListing(out folderNames, out fileNames);
// Copy StringArray to ArrayList to fit in Object variable
ArrayList fileNamesArray = new ArrayList(fileNames);
// Optional sorter
fileNamesArray.Sort();
// Fill ssis object variable
Dts.Variables["FtpFileList"].Value = fileNamesArray;
// Close connection
myFtpConnection.Close();
// Close Script Task, set result to success
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Fire error and set result to failure
Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}
Or VB.Net
' VB.Net code
Imports System
Imports System.Collections
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Try
' Get the ftp connection from the Connection Managers collection
Dim ftpServer As ConnectionManager = Dts.Connections("myFtpServer")
' Create a FTP connection object and us the credentials of connection manager
Dim myFtpConnection As FtpClientConnection = New FtpClientConnection(ftpServer.AcquireConnection(Nothing))
' Open the connection
myFtpConnection.Connect()
' Set work folder with the value of the variable
myFtpConnection.SetWorkingDirectory(Dts.Variables("FtpWorkingDirectory").Value.ToString())
' Create StringArrays for filenames and folders
' The folderNames aren't used, but is mandatory
' for the next method.
Dim fileNames() As String
Dim folderNames() As String
' Get a directory listing and fill the StringArray variables
myFtpConnection.GetListing(folderNames, fileNames)
' Copy StringArray to ArrayList to fit in Object variable
Dim fileNamesArray As ArrayList = New ArrayList(fileNames)
' Optional sorter
fileNamesArray.Sort()
' Fill ssis object variable
Dts.Variables("FtpFileList").Value = fileNamesArray
' Close connection
myFtpConnection.Close()
' Close Script Task, set result to success
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Fire error and set result to failure
Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
End Class
5) Foreach Loop
Add a Foreach Loop after the Script Task. Edit the Foreach Loop and on the Collection tab select Foreach From Variable Enumerator as enumerator and select the object variable FtpFileList as the variable.
 |
| Foreach From Variable Enumerator |
6) Foreach Loop Variable Mappings
On the Variable Mappings tab of the Foreach Loop select the variable FtpFileURL.
 |
| Variable Mapping |
7) FTP Task General
Add a FTP task within the Foreach Loop. Edit the FTP Task and on the General tab you can select the FTP Connection which we created in step 2. And you can give it a suitable name.
 |
| General: select the ftp connection |
8) FTP Task File Transfer
On the File Transfer tab you can select the DownloadDirectory variable for the local folder. In the Operation category you should select Receive files as the operation. For RemotePath you can enter a dummy value because we are overriding that value with an expression in the next step.
 |
| File Transfer |
9) FTP Task Expressions
At the Expressions tab you must add an expression for the RemotePath: @[User::FtpWorkingDirectory] + @[User::FtpFileURL] This expression combines the working folder (/root/data/) and the filename (01-01-2011.csv) into: /root/data/01-01-2011.csv.
 |
| Click at 2 to open the Expression Editor |
10) Process file in Data Flow
Add a Data Flow Task within the Foreach Loop to process the downloaded files.
 |
| Process files |
10) Process file in Connection Manager
Create a Data Flow like the example below. I named the connection for the csv file myDownloadedFtpFile. Go to the properties of that connection and add an expression for the ConnectionString so we can use it in the Foreach Loop: @[User::DownloadDirectory] + @[User::FtpFileURL]
 |
| A simple data flow |
 |
| Expression to override the ConnectionString property |
11) The result
Run the package to see the result.
 |
| The result |
Download example package
Note: In the Script Task you can use the .Net variable folderNames to create some recursive function to loop through all subfolders as well.
Note: If you just want to download all files with a Script Task and use a Foreach Loop file enumerator to process all files, you can replace the C# lines 48 - 55 or the VB.Net lines 41 - 48 with the following code:
// C# Code
// Download all files at once. Don't forget to add the SSIS variable DownloadDirectory to the ReadOnlyVariables
myFtpConnection.ReceiveFiles(fileNames, Dts.Variables["DownloadDirectory"].Value.ToString(), true, false);
' VB.Net code
' Download all files at once. Don't forget to add the SSIS variable DownloadDirectory to the ReadOnlyVariables
myFtpConnection.ReceiveFiles(fileNames, Dts.Variables("DownloadDirectory").Value.ToString(), true, false)