Using an SFTP/FTP Process – Pre-Requisites Guide

Server Requirements and Configuration

Workbooks provides all customers with the scripts necessary to configure an SFTP/FTP Import or Export process out of the box via our Script Library. However, there are some pre-requisites that need to be considered before you attempt to configure this functionality.

• SFTP/FTP Server: You will require a server that is either hosted in the cloud (e.g. DigitalOcean) or internally by your business. In both cases, it must be publicly accessible via the Workbooks IP Address Ranges.
• Server Specification: You/your IT team should evaluate:
  • How many files will be written to the server in a given period?
  • What is the typical file size?
  • Whether files should be archived or deleted after processing. If archived, how long should they be kept?

• Provisioned Server Details: Once the server has been provisioned, you will need:
  • Server’s public IP address or web address
  • Authentication method: either Username and Password or Public/Private Key Pair

Authentication Notes

• Public/Private Key Format: Keys must be generated using SSH-DSA format (not SSH-RSA).
• Key Encryption: Keys must not be encrypted (i.e. no passphrase).
• Linux Servers: The public key must be uploaded to the server’s authorized_keys file under the SSH directory.

Connection Details

• Protocol: Typically SFTP is used, though standard FTP is also supported.
• Port: Default is usually port 22 but this may vary depending on your organisation.

Directory Structure

Your server will need to be configured with a readable/writable directory within the home directory of the user that the process will be logging in as. For example, assume we have a user called Jim Bob and this is who we are authenticating as – Jim would need Read and Write access to the following folders and subfolders:

We might create a folder in Jim’s home directory called Workbooks, which is our top-level folder.

Under this folder, we might create the following sub-directories:

• Import: The folder where CSV files will be stored by a 3rd party. These files will be picked up by Workbooks and automatically imported.
• Export: The folder where Workbooks Reports will be stored when they are exported as CSV files. In preparation for a 3rd party service to process.
• Processed: The folder where Workbooks will move any files that it automatically imports once they have been processed. Note: This is only used if you are moving files rather than deleting them after processing.

Regardless of whether you are using an Import or Export process (or both), you will need to know the directory to the folders created above. On Linux-based systems, if we create a folder in the home directory, this may look something like:

• Home directory: /home/jimbob/
• Import Folder: Workbooks/Import
• Export Folder: Workbooks/Export
• Processed Folder: Workbooks/Processed

For Import Processes only, you will need an example file that can be used to set up the field mappings for the automated import. This can be a small file with column headers and one record.

For Export Processes, you will need to create a report of the data that will be exported to the SFTP/FTP server for processing by a third party.