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.

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.Succes
s

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.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Office Mobile Apps To End as Microsoft Highlights New Office App

    Microsoft plans to end support for Windows 10 Mobile applications on Jan. 12, 2021, according to a Friday announcement.

  • Is Microsoft Finally Reinventing Office?

    Microsoft is testing out a new technology called "Fluid Framework." It could mean that Brien's dream of one Office app to rule them all might soon become reality.

  • Azure Active Directory Connect Preview Adds Support for Disconnected AD Forests

    Microsoft on Thursday announced a preview of a new "Cloud Provisioning" feature for the Azure Active Directory Connect service that promises to bring together scattered Active Directory "forests."

  • Microsoft Defender ATP Gets macOS Investigation Support

    The endpoint and detection response (EDR) feature in Microsoft Defender Advanced Threat Protection (ATP) has reached the "general availability" stage for macOS devices.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.