Configuring SFTP ETL with Workato

Jayden Bartram
May 26 · 4 min read

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 1. Create a Workato recipe that is triggered by webhook

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

workato sftp couchdrop etl
workato sftp couchdrop etl
Configuring a SFTP workflow with Workato

Step 2. Configure Workato JSON webhook

Select guided setup and provide an event name

workato sftp workflow
workato sftp workflow
Configure webhook using guided mode

Ensure the Webhook type is ‘JSON payload’

workato ftp etl
workato ftp etl
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 sftp
couchdrop sftp
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.

workato sftp
workato sftp
Configuring Workato SFTP connection
couchdrop ftp etl workato
couchdrop ftp etl workato
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.

workato ftp
workato ftp
Configuring the upload location for the file
workato couchdrop ftp automation
workato couchdrop ftp automation
Configured workflow
couchdrop workflow file automation
couchdrop workflow file automation
Workflow in action
couchdrop cloud sftp
couchdrop cloud sftp
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.

Couchdrop

Couchdrop is your cloud SFTP server and virtual file system for the cloud.

Couchdrop

Couchdrop is your cloud SFTP server. It helps you SFTP/FTP, SCP and Rsync directly into your cloud storage. Couchdrop is developer ready and offers a API and webhooks. It works with all widely used cloud storage endpoints, such as Dropbox, Box, Google Drive, Wasabi, S3, etc.

Jayden Bartram

Written by

CEO of Movebot and Couchdrop. Also have a strong passion to help businesses and people optimise and grow — jbconsulting.co.nz for more.

Couchdrop

Couchdrop is your cloud SFTP server. It helps you SFTP/FTP, SCP and Rsync directly into your cloud storage. Couchdrop is developer ready and offers a API and webhooks. It works with all widely used cloud storage endpoints, such as Dropbox, Box, Google Drive, Wasabi, S3, etc.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store