Placeholder

Customer Forum

SFTP/FTP Automated Import Summary Script

Sam (Workbooks Online) Posted: 2018-11-12 09:18
If you are using our SFTP/FTP Plugin, Stage 1 of this guide is completed automatically and can be skipped. See here for more information. If you are using a custom-built SFTP/FTP Process you should follow all steps of this guide.

This Script allows a Scheduled Process to be created which summarises the results of automated Imports created through an SFTP/FTP Process. This information can then be reported on and sent out in a Scheduled Email to the relevant people in your organisation to monitor the status of your imports. 

Stage 1 - Create the Process

To create a Scheduled Process navigate to Start > Configuration > Automation > Processes > Scheduled Processes, once on this page select New Scheduled Process.

From here, there are several fields you will need to complete:

  • Name: This can be anything you like to easily identify the process but we recommend “SFTP Summary Process”.
  • Script: Choose the Script Library option and then select the "SFTP Summary Report" Script in the 2nd picklist. Using the Script Library version allows you to stay up to date with any changes made by our development team in the future.
  • Process Schedule: This is entirely up to you, as you will need to consider how often your SFTP/FTP Process creates Imports, how many processes you have, and how quickly you want to be informed of any potential issues. 
  • Run As: Set to the Automation User. This prevents User related issues such as Accounts or Passwords no longer being valid which will reduce the chances of the Process not running.
  • Finally, you must decide what actions you want the Process to take if it fails, whether it needs to Run again or disable itself and which User needs to be notified.

sftp1.png

This process will automatically retrieve any completed Import Jobs that have been approved automatically. Import Jobs can only be set to "Auto-Approve" if they are created via the Workbooks API meaning this process will only find Import Jobs that have been created via the SFTP/FTP Scheduled Process, ignoring those that are created via the Workbooks Desktop.

Stage 2  - Configure the Report

We have created a Template Report for this Process to work with, however you may want to make some amendments to the Report Criteria to work with your specific requirements.

The Report Template can be found from Start > New > Report > Create a Report from a Template Report, then select “Template - API Data - SFTP Import Summary Report”.

This will load the Report shown below:

sftp2.png

Criteria that you may wish to consider

  • Only show Imports with Errors: The Report contains a Calculated Column “Errors Encountered” which SUMs the Total Error and Total Warnings columns in the report. You could apply a Calculated Criteria to show where this Column is “Yes”.
  • Only show Imports created in a given time frame: A Criteria can be applied to the "Created at" field to only show Imports created today, or within the last 2 hours. This Criteria may vary depending on how often your SFTP/FTP Imports run, how many different SFTP/FTP processes you have and how often you may want to review the Errors and Warnings.

Step 3 - Setup a Scheduled Email (optional)

Rather than having to periodically check the above Report to review Imports, you can create a Scheduled Email to send a summary of automated Imports to the relevant Users in your organisation. Details on how to setup and configure Scheduled Emails can be found on our Knowledge Base

When setting up this Scheduled Email, consider how often you would like to be notified of any errors - this will need to work in line with your Report’s criteria and how often your processes are running. For example, if you have lots of processes that are dealing with specific files that run every hour, your report will need to take this into consideration. If you added a criteria that only shows Imports from the last hour, you could end up losing visibility of vital Import information. Lastly, we would also recommend only sending the Report when it is not empty, especially if you are running this frequently.

Step 4: Setup a Scheduled Bulk Action (optional)

A Scheduled Bulk Action can be configured to remove API Data records that are no longer of relevance automatically. This can be setup from the API Data Landing Page under Configuration > Automation > API Data. In the same manner as a Scheduled Email, we'd recommend considering how often, and how many SFTP/FTP scripts you have on your database. For example, you wouldn't want to delete API Data records that have not yet been set out in a Scheduled Email. For more information on setting up a Scheduled Bulk Action, see our Knowledge Base here.

All of these tools together will now provide you with a better method to monitor your SFTP/FTP Imports and any potential issues they may encounter.