Configuring SFTP ETL with Integromat

Integromat is a no-code automation platform that integrates with most cloud applications globally. Utilising Couchdrop’s cloud SFTP you can create a fully functioning extract, transform, load (ETL) process where you can either process customers data on upload, or send your vendor data based on an action.

Couchdrop’s SFTP offers not only a traditional SFTP server, but also modern features such as webhooks, API and advanced security for additional users or your platform as a whole. You can restrict users to specific directories and have an array of webhooks and actions based on each users directory. Couchdrop SFTP also enables you to connect your cloud storage platform as the storage backend, regardless if this is Azure Blob, SharePoint, S3, Dropbox or anything in between.

A great example would be a finance company who receives financial information from external clients into their Couchdrop instance via SFTP or the web portal. From there a webhook would be sent to an automation system 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 Integromat.

The steps required:

• Step 1. Create Webhook connection
• Step 2. Copy Webhook URI to Couchdrop SFTP
• Step 3. Configure JSON Parser module
• Step 4. Configure SFTP module
• Step 5. Conduct operation on file

Integromat modules required:

• Webhook
• SFTP
• JSON Parser

Step 1. Create Webhook connection

Create the webhook connection and copy the address to clipboard.

Configure Integromat webhook module

Step 2. Copy Webhook URI to Couchdrop

Under your Couchdrop portal either locate the directory you wish to receive webhook alerts on and access under its properties, or amend at the global account level.

Paste your Integromat webhook URI

Step 3. Configure JSON Parser module

Add the JSON Parse module and create a data structure, use the generator and paste the below sample Couchdrop webhook output.

Paste below Couchdrop SFTP webhook sample into “sample data”

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"
}

Add the object (value) from the webhook as the string.

Add the webhook value into JSON string field

Step 4. Configure SFTP module

For this scenario we have selected the Get File operation from the SFTP module. Configure your SFTP connection using sftp.couchdrop.io as the host and your Couchdrop username and password.

Configure Couchdrop SFTP in the SFTP module

For the File Path select the filename object from the parsed JSON

Copy across the filename object into the file path field

Step 5. Conduct operation on file

For this scenario we are going to upload the file to a different SFTP location and then delete it from the source. This is where your ETL operation would initiate.

Example of an SFTP workflow

Couchdrop’s audit log to show the actions