FTP SSL

The Switcboard FTP SSL Connector provides automated, scheduled ingestions of data from FTP servers over SSL in a variety of formats.

• Prerequisites
• Scheduling
• Sample Switchboard Script
  • Parameters
• Specifying Patterns
  • File Patterns
  • Datetime Patterns
• File Formats and Encoding
  • File Formats
  • Encodings
  • Compression

Prerequisites

To configure access to the FTP SSL Connector, you need:

• FTP Hostname
• An account Username
• An account Password
• a port

Scheduling

The FTP SSL connector can be scheduled to run multiple times a day at user-defined hour and timezone.

• To configure this schedule, use the delay_hours parameter.
• By default, the connector will run once at 6am PT.

See Hourly Scheduling for more information.

The connector can be backfilled depending on the filename (see below).

Using Switchboard Static IP

If necessary due to IT or security policy, this connector can be configured to route traffic through one of Switchboard’s static IP addresses. To do so, include the parameter static_ip: true; in the Switchboard Script import statement.

Sample Switchboard Script

download t from {
            type: "ftp_ssl";
            key: "ftp_ssl_key";
            pattern: "/Acccounting_Seed_Exports/*.csv";
            datetime_pattern: "*/*-YYYY-MM-DD*.csv";
        } using {
            name: string;
        };

Parameters

datetime_pattern string

optional

An expression representing data and time information appended to a file name. See Specifying Datetime Patterns below.

Example: */*-YYYY-MM-DD*.csv

format string

optional

Specifies a format type. See File Formats below.

Example: csv

pattern string

required

An expression representing the path and file name of files to pull in. See Specifying File Patterns below.

Example: /Acccounting_Seed_Exports/*.csv

Specifying Patterns

Specifying File Patterns

Switchboard matches target SFTP files based on wildcard patterns or regular expression. Switchboard polls the source bucket for new files that match the pattern or regular expression provided. By default, Switchboard re-ingest files upon detection of source file checksum.

To specify a file match by pattern, use the pattern parameter in your SBS. The * character is used as a wildcard pattern match:

pattern: "/folder/my_pattern*";

To specify a file match using a regular expression, use the regex parameter containing a valid matching pattern:

regex: "/folder/my_pattern(a|b)_\d{6}.csv";

Specifying Datetime Patterns

You can configure Switchboard to poll for file names that match a date pattern. This allows importing the date-range backfill in the Switchboard UI. 

To do this, add the datetime_pattern to the import script. Since target objects may have multiple dates in the filename, it is important to specify a pattern that matches the specific date string required.

To match the first date string in an file name of /source_folder/my_file_name_2020-01-01_2020_01-08.csv, use the following pattern:

datetime_pattern: "*my_file_name_YYYY-MM-DD_*";

To limit the number of previous days that the DateTime pattern applies, use the lookback_days parameter. If no files are found for the lookback period, Switchboard will consider this an error. 

The example below shows locate files with a DateTime pattern that matches files within the past 5 days:

lookback_days: 5;

You can also use the delay_hours parameter to specify the in hours the system waits between the previous update and the next update after midnight

File Formats and Encoding

Switchboard imports files with a variety of formats, encodings, and compression schemes.

File Formats

To specify a format type, use the format parameter:

format: "csv";

The available options are:

CSV specific options

The following parameters may be added to your SBS when dealing specifically with CSB files.

delimiter character

optional

Delimiter character used to separate fields.

e.g., comma: “,” pipe: “|” tab: “\t” thorn: “þ” space: “ “ caret: “^” semicolon: “;”

header_row Boolean

optional

Specifies to skip the first row and treat it as a header row

postamble_rows Int

optional

Count of trailing rows to skip

preamble_rows Int

optional

Count of leading rows to skip

Encodings

By default, Switchboard files are encoded in UTF-8. Switchboard uses standard Java Charset encoding format strings. 

To specify a file encoding using Latin Alphabet No. 1, provide the encoding parameter in the  import statement:

encoding: "ISO-8859-1";

Compression

Switchboard ingests files in gzip or zip compression formats. 

To specify files in gzip format, provide the parameter in the import statement:

compression: "gzip";