Using the FTP Task in an SSIS Package

Pulling data from different sources is easy. One of the easiest is via the FTP Task.

When designing SSIS packages, you will find that you often have to pull data from many different sources. Although FTP is an older technology, it's one that hasn't quite lost its way in modern IT shops.

FTP is a simple and cheap way to make files available via the Internet; so, it is only natural that SSIS would contain a built-in method for retrieving files from FTP. In the Control Flow you have an FTP Task that is available to your package and we will look at how it is configured. The FTP Task is pretty simple, but you do a have a few options to explore. First off, let's talk about what the FTP task can do. The following are FTP operations you can perform:

  • Send Files
  • Receive Files
  • Create Local Directories
  • Create Remote Directories
  • Remove Local Directories
  • Remove Remote Directories
  • Delete Local Files
  • Delete Remote Files

All of these operations can be done by hard-coding things like paths or file names, or by setting these options with SSIS variables. When sending or receiving files, you can use a wildcard to retrieve multiple files. For example, specifying "*.txt" as the RemotePath for a Receive Files operation will pull all files ending in ".txt" from the FTP server.

You don't get a lot of control, however, and that is where the task breaks down in its usefulness. For example, I can download all text files on an FTP using a wildcard, but I can't get a list of files and then write logic to choose the files I want.

The bottom line is it will do one of the operations listed above very well in a batch or "blind" mode. When I say "blind", I mean that if you tell it to create a Remote Directory, it will try; there is no way to check for existence of said directory first. If you need to be a little more advanced with your FTP you can write a Script Task that utilizes FTP objects. As luck would have it, setting up FTP in an SSIS script will be the topic of discussion next week.

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.