Rollin' with the FTP Task Flow

Rolling a script to perform the FTP functions will allow you more flexibility than the built-in task and more productivity.

• By Eric Johnson
• 05/24/2010

In my last post, I talked about setting up and using FTP in an SSIS package. I talked specifically about using the FTP Task in the control flow and how it is slightly limited in its functionality.

This time, let's write a script that uses the FTP objects to provide more control over the FTP process. In this example, I will build a script that can selectively download files from FTP based on the results of a T-SQL query.

Take a look at the example and then we will walk through it:

Dim RegexFileName As Regex
Dim strFTPDir As String = "/"
Dim ftp As FtpClientConnection = New FtpClientConnection(Dts.Connections.Item("FTP").AcquireConnection(Nothing))
Dim conCtrlDBd As SqlConnection = CType(Dts.Connections("Control").AcquireConnection(Nothing), SqlConnection)
Dim strLocalFolder As String = "C:\FTPData"
Dim strRemoteFileName(0) As String
Dim cmdSitesQuery As New SqlCommand
Dim drSites As SqlDataReader
Dim i As Integer
Dim strRegEx As String

'Prepare Regular Expression for configured sites
With cmdSitesQuery
  .Connection = conCtrlDBd
  .CommandType = CommandType.Text
  .CommandText = "SELECT name FROM sites WHERE active = 1"
  drSites = .ExecuteReader
End With

While drSites.Read
  strRegEx = strRegEx & drSites.Item("name").ToString & "|"
End While
drSites.Close()

'strip off trailing |
strRegEx = Left(strRegEx, Len(strRegEx) - 1)
strRegEx = "(" & strRegEx & ")_data"

RegexFileName = New Regex(strRegEx)

'Connect to the ftp server
ftp.Connect()
ftp.SetWorkingDirectory(strFTPDir)

'Build a array of all the file names that are going to be FTP'ed
Dim arrFiles() As String
Dim arrFolders() As String

ftp.GetListing(arrFolders, arrFiles)

'Loop and download where filename matches regular expression
For i = 0 To UBound(arrFiles) - 1
  If RegexFileName.IsMatch(arrFiles(i)) Then
    strRemoteFileName(0) = strFTPDir & "/" & arrFiles(i)
    ftp.ReceiveFiles(strRemoteFileName, strLocalFolder, True, True)
  End If
Next

conCtrlDBd.Close()
ftp.Close()
Dts.TaskResult = Dts.Results.Success

First off, I set up all the variables I am going to need; most of these are pretty simple, but do note that the first two variables reference DTS Connections. These refer to connection manager objects that exist in the SSIS package. By referencing this way, I can still control the location of my FTP server and my database server from outside of the script.

In the first section of real code, I create a regular expression from the results of a SQL Query. The query, "SELECT name FROM sites WHERE active = 1" returns a list of all active sites. I then use this list to build a regular expression that would look something like this (in the example, I use three site names):

"(Denver|ColoSprgs|Greeley)_data"

This allows me to later match all the FTP files that match the expression, "Denver_Data" for example.

In the next section I use the FTP object to connect, set a working directory, and dump out a list of files and folders to a couple arrays. This allows the script to examine the files before downloading anything.

Lastly, I just have to loop through each file and download it if it matches my regular expression.

That is pretty much it. Rolling a script to perform the FTP functions will allow you far more flexibility than the built-in task and that can mean more productivity and less time downloading things you don't need. Happy coding.