Update a table with files from an SFTP server as a chain

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

1. In Chain Builder, from Build  device_hub, Chains, click Create Chain.
   
2. Under Setup, enter a unique name and description to help identify the chain and its intent.
3. Under Variable, click Add, and enter a variable for table_id.
4. Click Save.

Step 2. Add the SFTP List Directory command

1. Under Available BizApps, select SFTP Connector, and move List Directory to Start.
2. Click Edit create for List Directory.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. 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.
5. For Detail level, select Full.
6. Click Save.

Step 3. Create a command group to download files and update the table

1. Click Command Group repeat, and move Command Group format_list_numbered to the canvas.
2. Drag a link from Start to the group's In, and click the group's Edit create.
   
3. Enable Iterations, and select the Remote files output under List Directory.
4. Click Save.
5. Under Available BizApps, select SFTP Connector, and move Download File to the canvas.
6. Drag a link from Group Start to Download File, and click Edit create for Download File.
   
7. For File, enter ., and append the Remote files iteration variable under Group Iterator.
8. Click Save.
9. Click Events insert_invitation, and move Run Chain device_hub to the canvas.
10. Drag a link from Download File to Run Chain, and click Edit create for Run Chain.
    
11. Under Basic Info, enter a unique name and description to help identify the event.
12. Under Chain to Run, select the chain created to update a table with any file.
13. 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.

14. For Table ID, select the table ID variable under Chain.
15. For Data, select the Downloaded File output under Download File.
16. Click Save.

Step 4. Finalize the chain

To run the chain:

1. 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.

2. Click Publish  publish, Execute  flight_takeoff and Run Chain.

Note: For personalized assistance specific to your organization, contact Workiva Support.