With this chain, you can automatically update a table with a dataset downloaded from a Secure File Transfer Protocol (SFTP) server to a table. For example, if you export files from your system of record to an SFTP server, you can run this chain to automate importing them into a table.
Ingredients
Before you create this chain, first set up a chain to update a table with a dataset from any source.
To create this chain, first you'll need:
- An SFTP Connector set up for the server with the files to import
- A Run Chain event to include the chain to update a table with any file
Step 1. Create the chain
- From Chains , click Build
- Under Setup, enter a unique name and description to help identify the chain and its intent.
- Under Variable, click Add, and enter a variable for table_id.
- Click Save.
Step 2. Add the SFTP List Directory command
- Under Available BizApps, select SFTP Connector, and move List Directory to Start.
- Click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- In Directory, enter the name of the file or directory to list. To list multiple files or directories, use an asterisk
*
as a wildcard character. - For Detail level, select Full.
- Click Save.
Step 3. Create a command group to download files and update the table
- Click Command Group , and move Command Group to the canvas.
- Drag a link from Start to the group's In, and click the group's Edit
- Enable Iterations, and select the Remote files output under List Directory.
- Click Save.
- Under Available BizApps, select SFTP Connector, and move Download File to the canvas.
- Drag a link from Group Start to Download File, and click Edit
- For File, enter
.
, and append the Remote files iteration variable under Group Iterator. - Click Save.
- Click Events , and move Run Chain to the canvas.
- Drag a link from Download File to Run Chain, and click Edit
- Under Basic Info, enter a unique name and description to help identify the event.
- Under Chain to Run, select the chain created to update a table with any file.
- In File Prefix, enter a prefix–such as sftp-data–to identify earlier versions of the dataset to replace with the new file.
Tip: To append datasets rather than replace them, append the Chain.ExecutionDateTime runtime variable to the prefix to ensure a unique value each time the chain runs. To simplify the value, apply a Parse Date/Time variable transformation with a format of
%m%Y
. - For Table ID, select the table ID variable under Chain.
- For Data, select the Downloaded File output under Download File.
- Click Save.
Step 4. Finalize the chain
To run the chain:
- Click Chain Settings, enter the table's ID as the value for the Table ID chain variable, and click Save.
Note: In your browser's address bar, the table ID is the alphanumeric string after
tables/
in the table's URL. - Click Publish , Execute and Run Chain.