Customer Forum

Using an SFTP/FTP Process - Pre-Requisites Guide

Jamie (Workbooks Online) Posted: 2019-01-17 17:11

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.

  • First and foremost, you will require an SFTP/FTP Server that is either hosted in the cloud with a service such as DigitalOcean, or it could be hosted on an internal server owned by your business.
    • In both instances, the server must be publicly accessible via the Workbooks IP Address Ranges as defined here.
    • You will need to consider the specification of the server hardware in regards to CPU power, RAM and Hard Drive capacity. This will vary depending on a number of factors, but you/your IT team should consider the following:
      • How many files will you will be writing to the server in a given period of time and how big will the files be?
      • Do you wish to archive the files after they have been processed? Or do you want to delete them? If they are being archived, how long do you want to archive the files for?
        • NOTE: Workbooks recommends that files are archived for easier debugging and re-running of a file in the event of an issue.
  • Once your IT team have provisioned a new server, will need to consider the following configuration and provide the details specified:
    • At a minimum you will need the server’s public IP address (or web address), and a method of authentication from one of the following:
      • Username and Password
      • Public/Private Key Pair values
        • The Public/Private Key Pair should be generated in SSH-DSA format. SSH-RSA is not supported.
        • The Public/Private Key Pair must not be encrypted (no passphrase)
        • If your server is installed with Linux, the Public Key must be uploaded to your server in the “authorized_keys” file under the SSH directory
    • You will need to know the protocol that is allowed by the server to connect. Typically, this would be a secure connection using SFTP. However, the Workbooks process also supports standard FTP.
    • You will need to know the port that will be used for connecting to the server. The default is usually port 22, but this might be different for your organisation
    • 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 to Read and Write access to the following Folders/Sub Folders:
      • We might create a folder in Jim’s home directory called “Workbooks” which is our top level folder.
      • Under this folder, we might create some sub-directories called
        • “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 folder 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 1 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

Please note: Workbooks Support are unfortunately unable to assist with the setup of an SFTP/FTP Server. You will need to reach out to your IT team to configure this and provide you with details mentioned in this section.