Configuring SFTP ETL with Workato

Workato supports native SFTP as a platform to import and export files. By using this native integration you can utilise it for your extract, transform and load (ETL) workflows and file automation requirements. The key challenge is having a scalable, fully featured SFTP server that is low maintenance and works within minutes. Couchdrop’s cloud SFTP solution can help with your automated workflows by utilising its native SFTP service, webhooks, API, cloud storage integrations and ability to create and lock down users.

ETL Use Cases:

• As a vendor your clients send you files via SFTP (or another means such as web portal) where you can receive a webhook event on upload to then initiate your ETL process.

Have your clients send you data via SFTP to then be processed through automated ETL operations

• As a client you can expose your data to your vendor for them to then process the uploaded file on a webhook event.

Expose data to your vendor to be automatically pulled into ETL operations

An example of an ETL operation with Workato would be a finance company who receives financial information from external clients via Couchdrop SFTP or Couchdrop’s web portal. From there a webhook would be sent to Workato which will then pull down the file that was uploaded and perform ETL operations as required.

This article will run you through an example of a workflow using Couchdrop’s SFTP and Workato.

The steps required:

• Step 1. Create a Workato recipe that is triggered by webhook
• Step 2. Configure Workato JSON webhook
• Step 3. Configure Couchdrop with webhook URI
• Step 4. Configure SFTP module for file download
• Step 5. Conduct operation on file

Step 1. Create a Workato recipe that is triggered by webhook

Create a new recipe under your desired folder that is triggered from a webhook.

Configuring a SFTP workflow with Workato

Step 2. Configure Workato JSON webhook

Select guided setup and provide an event name

Configure webhook using guided mode

Ensure the Webhook type is ‘JSON payload’

Ensure the webhook is JSON payload and upload JSON sample

Use a JSON sample (see below) to provide the fields and generate the JSON schema

Upload the below sample to get the required webhook fields

Sample Couchdrop SFTP webhook output:

{
  "account": "demouser",
  "filename": "/demo/customers/bobsburgers/burgersaucereceipe.txt",
  "authenticated_user": "demo1",
  "storage_engine": "hosted",
  "storage_engine_id": "7e88f06d-3aa5-45d9-97c2-3c5fa28ca0b4",
  "event_type": "upload",
  "ip_address": "123.253.47.202",
  "success": true,
  "total_size": 40,
  "additional_info": "",
  "system": "sftp",
  "transaction_id": "836851c7-f745-4476-8a0a-b4df14c4cd0e",
  "region": "us1",
  "text": "File /demo/customers/bobsburgers/burgersaucereceipe.txt uploaded by demo1 via sftp from 123.253.47.202"
}

Step 3. Configure Couchdrop with webhook URI

Find your desired folder you wish to have this webhook trigger on and paste it under the folder properties then save.

Couchdrop’s interface — upload webhook URI

Step 4. Configure SFTP module for file download from Couchdrop

Configure the SFTP module in Workato using your Couchdrop credentials and sftp.couchdrop.io as the SFTP endpoint hostname.

Configuring Workato SFTP connection

Setting the filename object from the webhook as the file path

Step 5. Conduct operation on file

For this scenario we are repurposing the SFTP connection to download the file, upload it to another location and then delete it. This may not be the best example, but it shows the possibilities with SFTP. After the file is downloaded, you could upload it to a CMS or another solution to then be processed.

Configuring the upload location for the file

Configured workflow

Workflow in action

Couchdrop’s audit log to show the actions from the above workflow

To get up and running with Couchdrop’s cloud SFTP server and integrate it into your ETL process, navigate to Couchdrop’s website to sign up or learn more.