Configuring SFTP ETL with Workato

Workato supports native SFTP as a platform to import and export files. By using this native integration you can utilize 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. And Couchdrop is perfectly suited for this challenge. 

Couchdrop and ETL

Couchdrop’s cloud SFTP solution can help with your automated workflows by utilizing its native SFTP service, webhooks, API, cloud storage integrations, and the ability to create and lock down users.

ETL Use Cases

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

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

An example of an ETL operation with Workato would be a finance company that 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.

Couchdrop SFTP with Workato

By following a few short steps, you build out a workflow using Couchdrop's SFTP and Workato. You'll need to complete these steps for your workflow: 

• 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 operations on file

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

In Workato, create a new recipe under your desired folder that is triggered from a webhook.

Step 2. Configure Workato JSON webhook

Still in Workato, select guided setup and provide an event name. 

Ensure the Webhook type is ‘JSON payload’

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

Sample Couchdrop SFTP webhook output:

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.

 

Step 4. Configure SFTP module for file download from Couchdrop

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

Configure additional actions in Workato as needed. For this scenario, we are repurposing the SFTP connection to download the file, uploading it to another location, and then deleting it.

Step 5. Conduct operations on the file

As mentioned above, we are downloading a file, re-uploading it to a new location, then deleting it. But you can do any other action as needed for your workflow. For instance, after the file is downloaded, you could upload it to a CMS or another solution to then be processed.

The workflow is now configured in Workato. 

Once configured, make sure to test the recipe in Workato and ensure everything is working as expected. 

SFTP ETL with Workato using Couchdrop Automations

Alternatively, many of these steps can be done from within Couchdrop using Couchdrop's automations. These automations include many file actions including moving, renaming, encryption/decryption, firing a webhook, and more.

Choose whichever platform you're more comfortable with for building out automations, they should work either way with a correct configuration. For more information on Couchdrop automations, see our article Simplifying tasks with Couchdrop Automations. 

Want to try this combination for yourself? You can test configuring SFTP ETL with Workato using Couchdrop by starting a free 14-day trial. You don't need to add a credit card and there's no sales or demo calls required if you don't want one. You can start up and go without any hoops to jump through. Click here to start your free trial now.