The Multi-File Stage s9600 template provides a simple method to stage many files with the same structure. The following steps describe how to setup this component.
1. Click “create new” from the stage component page. Find s9600 and download the “File”. This file is an xls file which contains the configuration details for the Multi File Stage tasks.
2. Click on the template id, s9600. 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 Multi-File Stage component connection property to utilize this newly created connection.
That’s all. Now you can open the configuration file and define the multi file stage tasks that you want to complete. One or more file stage components can be utilized in the same configuration file. 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 a source 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 multi-file stage 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 directories before transfer option
- Wildcards for file names enable a group of files to be staged with a single definition
- Logging is automatic. Configuration is centralized.
- File system and MSDB package store is supported
- Search all subfolders for files to stage
- Move successfully staged files to a processed folder & failures to and error folder
- Apply suffix and/or prefix to archived files
Below is a list of fields included in the configuration file.
| Active |
| Component Package Store |
| Component Package Directory |
| Component Package Name |
| Component Truncate Target |
| Source File Directory |
| Traverse Subfolders |
| Source File |
| Extract |
| Retain Directory |
| Overwrite |
| Delete Archive |
| Processed – Archive |
| Processed – Clear |
| Processed – Overwrite |
| Processed – Directory |
| Processed – Prefix |
| Processed – Suffix |
| Error – Archive |
| Error – Clear |
| Error – Overwrite |
| Error – Directory |
| Error – Prefix |
| Error – Suffix |
| 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).
