Configuring SFTP connections

Introduction

The Patchworks SFTP connector is used to work with data via files on SFTP servers in process flows. You might work purely in the SFTP environment (for example, copying/moving files between locations), or you might sync data from SFTP files into other connectors, or you might use a combination of both! For example, a process flow might be designed to:

1. Pull files from an SFTP server

2. Use the data in those files as the payload for subsequent processing (e.g. sync to Shopify)

3. Move files to a different SFTP server location

Show me

This guide explains the basics of configuring a connection shape with an SFTP connector.

Guidance on this page is for SFTP connections however, they also apply for FTP.

About the Patchworks SFTP connector

Authentication

When you install the Patchworks SFTP connector from the Patchworks marketplace and then add an instance, you'll find that two authentication methods are available:

Auth method	Summary
User pass	The instance is authenticated by providing a username and password for the SFTP server.
Key pass	The instance is authenticated by providing a private key (RSA .pem format) for the SFTP server.

Further information on these authentication methods can be found on our SFTP (prebuilt connector) page.

Endpoints

When you add a connection shape and select an SFTP connector, you will see that two endpoints are available:

Here:

• SFTP GET UserPass is used to retrieve files from the given server (i.e. to receive data)

• SFTP PUT UserPass is used to add/update files on the given server (i.e. to send data)

You may notice that the PUT UserPass endpoint has a GET HTTP method - that's because it's not actually used for SFTP. All we're actually doing here is retrieving host information from the connector instance - you'll set the FTP action later in the endpoint configuration, via an ftp command settings.

Configuring SFTP endpoints

Having selected either of the two SFTP endpoints, configuration options are displayed. The same options are used for both endpoints but in a different sequence, reflecting usage:

These fields are summarised below:

Option	Summary
FTP command	A valid FTP command is expected at the start of this field (e.g. get, put, rename, etc.). If required, qualifying path/filename information can follow a given command.
Root	This field is only needed if you are specifying a regular expression in the subsequent path field. If you are NOT defining the path field as a regular expression, the root field isn't important - you can leave it set to /. If you are ARE defining the path field as a regular expression, enter a root path that reflects the expected file location as closely as possible - this will optimise performance for expression matching. For example, suppose the files that we want to process are in the following SFTP folder: /orders/store/year/pending and that our specified path contains a regular expression to retrieve all files for store 1 for the current day in 2023. In this case our root would be defined as: orders/store1/2023/pending In this way, any regular expression to match for the path will start in the relevant (2023 )folder - rather than checking folders and subfolders for all stores and all years.
Path	If the name of the file that you want to target is static and known, enter the full path to it here - for example: store1/2023/pending/20230728.json When specifying a path to a given folder in this way, you don't need a / at the start or at the end. If the name is variable and therefore unknown, you can specify a regular expression as the path. In this case, you enter the required regular expression here, and ensure that the root field contains a path to the relevant folder (see above). Retrieve files from root with a common filename prefix Suppose our SFTP folder is: store1/orders/2023/pending ...and that it contains a number of files which all start with 'orders' followed by a date: Our path and root fields would be defined as below: Here, the root field is set to the folder containing our required files and the path field contains a regular expression where: the / at the start of the string denotes the start of the regular expression the ^ means that the first part of the line (i.e. filename) must start with the literal string orders [0-9]* matches any sequence of digits \. is an escaped full stop, so a literal full stop is used instead of the special meaning that a full stop has in regular expressions json is a literal string to be matched the / at the end of the string denotes the end of the regular expression Retrieve all files of a type from the root Suppose you have multiple .csv files to be retrieved from the root of your server. In this case, the root should be left blank and the path should be set as: /.*.csv/i For example: Here, the root field is empty and the path field contains a regular expression where: the / at the start of the string denotes the start of the regular expression. *. matches any character (.) zero or more times (*). The * ensures that any characters can appear before .csv element of the filename. \.csv matches the literal string .csv. The backslash (\) is used to escape the dot (.), making it match a literal dot, so the csv is matched exactly. / at the end of the string denotes the end of the regular expression. i is a flag that means 'case-insensitive'. This ensures that the regular expression will match both upper and lower case variations of the letters in the pattern. For example, it would match both .csv and .CSV.
Original filename	This field is not currently used. For information about working with original filenames please see the Using an {{original_filename}} variable section below.
Original path	This field is not currently used. For information about working with original paths please see the Using an {{original_path}} variable section below.

Valid FTP commands

The following commands can be used when configuring an SFTP connector:

• append

• copy

• delete

• list

• get_and_move

• pluck

• put_non_empty

• rename

• size

• modified

• mime

• exists

Using an {{original_filename}} variable

If you're processing files between SFTP server locations, the {{original_filename}} variable is used to reference filenames from a previous SFTP connection step. It's most typically used with the SFTP PUT UserPass endpoint. This handles cases where you're taking action with files/data processed by a previous connection shape which is configured to use the SFTP GET UserPass endpoint and retrieve files matching a regular expression path.

In this scenario, we can't know the literal name of the file(s) that the SFTP PUT UserPass endpoint will receive. So, by setting the path field to {{original_filename}}, we can refer back to the filename(s) from the previous SFTP connection step.

Example

The sample process flow below shows two connection shapes that are configured with SFTP endpoints - the first is to get files and the second is to put files: If we look at settings for the first SFTP connection, we can see that it's configured to get files matching a regular expression, in a pending folder: The regular expression is explained below:

• the / at the start of the string denotes the start of the regular expression

• the ^ means that the first part of the line (i.e. filename) must start with the literal string orders

• [0-9]* matches any sequence of digits

• \. is an escaped full stop, so a literal full stop is used instead of the special meaning that a full stop has in regular expressions

• json is a literal string to be matched

• the / at the end of the string denotes the end of the regular expression

It will match the following files:

These are:

• orders20230717.json

• orders20230718.json

• orders20230719.json

Now if we check settings for the second SFTP connection, we can see that it's configured to put files into a ready folder. By specifying {{original_filename}} in the path field, we are referencing filenames from the first SFTP connection.

So, the three files retrieved from the first SFTP connection step (from the pending folder) are copied to the ready folder in the second SFTP connection step.

Using an {{original_path}} variable

The {{original_path}} variable is used to replicate the path from a previous SFTP connection step. It's most typically used with the SFTP PUT UserPass endpoint.

This handles cases where you're taking action with files/data processed by a previous connection shape which is configured to use the SFTP GET UserPass endpoint to retrieve files matching a regular expression path and you want to replicate the source path in the target location.

Example

The sample process flow below shows two connection shapes that are configured with SFTP endpoints - the first is to get files and the second is to put files: Our aim is to copy files retrieved from an FTP location in the first connection step, to a second FTP location, using the same folder structure as the source.

If we look at settings for the first SFTP connection, we can see that it's configured to get files matching a regular expression, in a store1 folder: The path is added as a regular expression, explained below:

• the / at the start of the string denotes the start of the regular expression

• orders\/2023\/pending\/ is a literal string for the folder structure, with escaped forward slashes

• orders is a literal string

• [0-9]* matches any sequence of digits

• \. is an escaped full stop, so a literal full stop is used instead of the special meaning that a full stop has in regular expressions

• json is a literal string to be matched

• the / at the end of the string denotes the end of the regular expression

It will match the following files:

These are:

• orders20230717.json

• orders20230718.json

• orders20230719.json

Now if we check settings for the second SFTP connection, we can see that it's configured to put files into a path specified as {{original_path}}:

So, the three files retrieved from the first SFTP connection step are copied to the ready folder in the second SFTP connection step and the source folder structure is replicated:

Using a {{current_filename}} variable

The {{current_path}} variable is used to reference the filename within the current SFTP connection step. It's particularly useful when moving files - for example:

get_and_move:store1/completed_orders/{{current_filename}}

Creating SFTP folders dynamically based on timestamps

A fairly common requirement is to create folders on an SFTP server which are named according to the current date. This can be achieved using a custom script, as summarised below.

Script code

The following four lines of code should be added to your script:

$timestamp = round(microtime(true) * 100);
$meta = $data['meta'];
$meta['original_filename'] = 'FIXED_TEXT' . '_' . $timestamp . '.xml';
$data['meta'] = $meta;

Our example is PHP - you should change as needed for your preferred language.

SFTP connection shape path

The path in your SFTP connection shape should be set to:

{{original_filename}}

How it works

The data object in the script shape contains three items: payload, meta, and variables.

Our script code creates a timestamp, puts it in to the meta, and then puts the meta into the data. The SFTP shape always checks if there is an original_filename key in the meta and if one exists, this is used.

Syncing SFTP data to another target connector

Much of the information above focuses on scenarios where you are working with files between different SFTP locations. However, another approach is to take the data in files from an SFTP server and sync that data into another Patchworks connector.

When a process flow includes a source connection for an SFTP server (using the SFTP GET UserPass endpoint) and a non-SFTP target connector (for example, Shopify), data in the retrieved file(s) is used as the incoming payload for the target connector.

If multiple files are retrieved from the SFTP server (because the required path in settings for the SFTP connector is defined as a regular expression which matches more than one file), then each matched file is put through subsequent steps in the process flow one at a time, in turn. So, if you retrieve five files from the source SFTP connection, the process flow will run five times.

More information

For information about working with regular expressions, please see the link below:

W3Schools.comwww.w3schools.com