Using the FTP Task in an SSIS Package -- Redmondmag.com

Using the FTP Task in an SSIS Package

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

By Eric Johnson
05/17/2010

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

To Support AI Workloads, Microsoft Taps Lumen for Network Boost

With AI workloads demanding more from its datacenters, Microsoft is looking to outside partners for infrastructure support.
Which Enterprises Should Ditch Passwords? All of Them!

As security biometrics continue to sophisticate, many organizations are still using flawed passwords to safeguard their data. That needs to change. Now.
CrowdStrike Blames Internal Testing Faults for Mass Outage

In an initial post-mortem, Austin-based security firm CrowdStrike said that an issue in its testing software led to the company pushing through a faulty update that took down more than 8.5 million Windows systems last week.
Build Your Own Custom Copilot, Part 2: Making the Connection

Now that you have a plan, it's time to create your personalized Copilot.
Microsoft Releases CrowdStrike Outage Recovery Tool

Microsoft has released a tool to help recover affected systems after last week's global outage caused by a faulty update pushed through by security firm CrowdStrike.