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.

Featured

  • Microsoft Offers More Help on Windows Server 2008 Upgrades

    Microsoft this week published additional help resources for organizations stuck on Windows Server 2008, which fell out of support on Jan. 14.

  • Microsoft Ups Its Carbon Reduction Goals

    Microsoft on Thursday announced a corporatewide carbon reduction effort that aims to make the company "carbon negative" by 2030.

  • How To Dynamically Lock Down an Unattended Windows 10 PC

    One of the biggest security risks in any organization happens when a user walks away from their PC without logging out. Microsoft has the solution (and it's not a password-protected screensaver).

  • First Stable Chromium-Based Microsoft Edge Browser Released

    Microsoft on Wednesday announced the first release of its Chromium-based Microsoft Edge browser at the "stable" commercial-release stage.

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.