The FTP File Stage s9500 template provides a simple method to collect one or more files from an FTP server.  The following steps describe how to setup this component.

1.  Click “create new” from the stage component page.  Find s9500 and download the “File”.  This file is an xls file which contains the configuration details for the FTP tasks.

2.  Click on the template id, s9500.  Fill out the component form.  Leave the configuration connection blank for now.  Save the component.

3.  This component requires a connection to the downloaded configuration file.  Create a new connection using the Excel 97-2003 template (c1103) which points to the location where the configuration file is saved.  Be sure to check the “Header Row” option.  Update the FTP Stage component connection property to utilize this newly created connection.

 

 

That’s all.  Now you can open the configuration file and define the FTP collection tasks that you want to complete.  A description is found in the comments of each field.  Also, be sure to review the “Setup” worksheet.  If you want to extract compressed or tar’d downloads, then you’ll need to install 7-zip.  Details are on the setup sheet.

Why Excel?

All of this configuration information could be saved to a text file.  We chose to use Excel primarily for the ability to use formulas.  Let’s say you have an FTP site that saves files in a folder structure based on the time such as months or years.  With Excel you can create a formula that will create the source path dynamically.  The LeapFrogBI FTP component will open, calculate, save, and close the Excel file prior to consuming it’s contents.  To enable this feature set the “Calculate Workbook” flag to “Yes” in the setup sheet.

There are quite a few useful features built into this component.

  • Extract contents support zip, gz, tgz, and tar.gz formats
  • Option to calculate formulas in configuration workbook
  • Clear the target directory before transfer option
  • Wildcards for file names enable a group of files to be collected with a single definition
  • FTP settings such as timeout, retry, chunk size, passive mode can be set for each definition
  • Logging is automatic.  Configuration is centralized.

Below is a list of fields included in the configuration file.

Active
Server
Port
User name
Password
Time-out (in seconds)
Use Passive Mode
Retries
Chunk size (in KB)
Directory
File
ASCII Mode
Extract
Retain Directory
Delete Archive
Target Directory
Overwrite
Clear Before Load
Notes

 

Note:  If you are running Excel 2007 or Excel 2010 you may get an error (file is corrupt) when trying to open the 2003 Excel file.  This is due to a feature called “Protected View” which recognizes that the file originated from the internet.  The solution is to turn this feature off temporarily.  In Excel go to:

File > Options > Trust Center >Trust Center Settings > Protected View

Uncheck the option for enabling protected view for files originating from the internet (highlighted below).