In this exercise, we will create a Chain that illustrates how to download the most recent file from an SFTP Server based on the date and time stamp specified in the file name.

Primary Learning Objective

To highlight the Split feature of Variable Transformation.

Secondary Learning Objectives

This exercise also highlights iteration (looping), downloading data from an SFTP server, Dynamic Variables, Conditionals, and Tabular Transformation

Prerequisites

Configure SFTP and Handlebars BizApp Connections

Supporting Template

Variable Transformation: Split


Step 1: Create a Chain

  • From the Sample Workspace for Transformations Workspace, select the DEV Environment.

  • Add a new Chain.

    • Name the Chain Transformation QS - Variable Transformation: Split.

  • Add a Dynamic Variable to the Chain.

    • Name the Dynamic Variable Max Date and do not set an Initial Value.

  • Save the Chain.


Step 2: Get a List of the Files in the SFTP Server Directory

The first step in the process is to retrieve a list of all of the available files. We will loop over the list to find the most recent and then download it in a subsequent step.

For reference, files are named using the convention of DATE_TIME_DATA TYPE

  • Example: 20210620_135645_Customer Master.csv

🔎 This naming convention is important to remember as the exercise progresses.

  • From the SFTP BizApp, add the List Directory Command to the Start Node.

  • Configure the Command with the following:

Name

List Directory

Directory

/out/*

Detail level

Full

  • Save the List Directory Command


Step 3: Initialize the Dynamic Variable with a Value

Use the Set Dynamic Chain Variable Event to set an initial value for the Dynamic Variable. Since Dynamic Variables persist values across Chain executions (runs), this resets the baseline of the Dynamic Variable value. We use 19991231 (Dec. 31, 1999) as a baseline.

  • Add a Set Dynamic Chain Variable Event to the Chain.

  • Connect the Start Node (List Directory) to the Set Dynamic Chain Variable Event.

  • Configure the Event with the following:

Name

Set Dynamic Chain Variable - Initialize

Dynamic Variable (dropdown)

Max Date

Value

19991231

  • Save the Event.


Step 4: Add a Command Group to Loop Over the Files

Use a Command Group to enable iterating (looping) over the list of files found in the SFTP server directory. The Commands connected to the Group will be executed for each file found.

  • Add a Command Group to the Chain.

  • Connect the Set Dynamic Chain Variable - Initialize Command to In portion of the Command Group.

  • Edit the Command Group.

  • In the Command Group, enable the Iterations toggle.

    • Click the iterations input box. Expand the List Directory Command from the Variable selection pane and select Remote Files. The input box should contain the Remote Files bubble.

  • Save the Command Group.


Step 5: Get the Date & Time Created from the File Name

Use the Handlebars Command with Variable Transformation to extract the date and time from the file name. This step addresses the primary learning objective of this exercise.

  • Add a Render Text Template Command from the Handlebars BizApp to the Chain.

  • Connect the Group Start from the Command Group to the Render Text Template Command.

  • Edit the Command.

  • Name the Command Render Text Template - Date & Time.

  • In the Template parameter, enter the following:

{{DATE}}_{{TIME}}
  • Add two Variables to the Command by clicking the Add button twice in the Variables section of the Command configuration.

  • In the first variable:

    • Specify the Variable Name as DATE.

    • Click in the Variable Value input parameter

      • Expand Group Iterator from the Variable selection pane

      • Select Remote Files Iteration.

    • Click the Remote File Iteration bubble to assign a Variable transformation

      • Assign the Split transformation and specify the delimiter as forward slash (/). This transformation will split the full file path into a list containing the file path and the file name.

      • Assign an additional transformation to Pick from List and specify the value as -1. This transformation will take the last item from the list generated by the split (i.e., the file name).

      • Assign another Split transformation and specify the delimiter as underscore (_). This transformation will break the file name into a list containing the text between each underscore.

      • Assign a final transformation of Pick from List and specify the value as 0. This transformation will take the first item from the file name list (i.e., the date). Lists are zero-based which is why specifying zero selects the first item in the list.

  • Repeat the above steps for the second variable.

    • This variable should be named TIME.

      ⚠️ Be sure to adjust the transformations accordingly to retrieve the time stamp specified in the file name.

  • Save the Command.


Step 6: Check if the Date of the Current File is Newer Than Others

Use a Conditional to test if the date and time of the current file being processed in the loop is more recent than other files already processed in the loop. Leverage the value stored in the Max Date Dynamic Variable to compare against the current file. When the date and time is more recent than the date and time stored in the Max Date, the next step in the loop is executed; otherwise, no additional actions are performed within the loop.

  • Add a Conditional Event to the Chain.

  • Connect the Render Text Template - Date & Time to the Conditional Event.

  • Edit the Conditional.

  • Add two rule conditionals that need that both need to be true by clicking the +Rule button twice. Leave the evaluation condition as AND.

    • For the first condition:

      • Leave the data type as String.

      • Click In the input box, expand Group Iterator from the Variables selection pane and select Remote Files Iteration. Click the Remote Files Iteration variable bubble and assign a Lowercase transformation.

        • Click the blue plus (+) button & select Save.

      • Change the evaluation criterion from Is Blank to Contains.

      • In the input box to the right of the criterion, enter customer.

        • In this example, we only want to look for files containing the text customer. While required for this exercise, adding criteria such as this to production chains is optional but highlights another way to control which files in a directory are evaluated.

    • For the second condition

      • Leave the data type as String.

      • Click In the input box, expand Render Text Template - Date & Time from the Variables selection pane and select the Rendered Text output.

      • Change the evaluation criterion from Is Blank to Greater Than (>).

      • Click in the input box to the right of the criterion, expand Chain from the Variable selection pane and select Max Date.

  • Save the Command.


Step 7: Update the Max Date Variable with the Most Recent Date & Time

Use the Set Dynamic Chain Variable Event to update the value for the Max Date Dynamic Variable. This Event is only invoked if the Conditional in the last step evaluates to a true condition (i.e., the date and time of the file being processed is greater than the current date and time stored in the variable).

  • Add a Set Dynamic Chain Variable Event to the Chain.

  • Connect the Conditional to the Set Dynamic Chain Variable Event.

  • Configure the Event with the following:

Name

Set Dynamic Chain Variable - Latest

Dynamic Variable (dropdown)

Max Date

Value

Rendered Text output from the Render Text Template - Date & Time Command

  • Save the Event.


Step 8: Download the Most Recent File

Use the SFTP BizApp to download the most recent file from the SFTP server. The file to download is determined by the loop in the Command Group.

  • Add a Download File Command from the SFTP BizApp to the Chain.

  • Connect the Out from the Command Group to the Download File Command.

  • Edit the Command.

  • Name the Command Download File.

  • In the File parameter, enter the following:

/out/<Max Date>_Customer Master.csv
  • The <Max Date> value in the above should be replaced with the Max Date Dynamic Chain Variable.

    • After the second forward slash, expand Chain from the Variable selection pane and select Max Date.

  • Leave Parallel download unchecked.

  • Save the Command.


Step 9: Add the File Date & Time as a Column in the Data

Use the Tabular Transformation BizApp to add the date and time from the file name as an additional column in the data file. This step illustrates how to perform a lightweight transformation. The premise of this step is that the downstream application may contain a field that tracks the date and time that a record was last updated. By adding it to the data file, we can provide this data as part of the data payload.

  • Add an Insert Column Command from the Tabular Transformation BizApp to the Chain.

  • Connect the Download File to the Insert Column Command.

  • Edit the Command.

  • Configure the Command with the following:

Name

Insert Column - Last Update

Input File

Downloaded File Output from the Download File Command

Output File

<leave blank>

Preview Results

Checked

Delimiter

Comma

Header Text

Last Update

Data Value

Max Date Dynamic Chain Variable

Insert Index

0

  • Save the Command.


Step 10: Test the Chain

Now that the Chain is complete, we can test the result.

  • Publish the Chain.

  • Click Execute and then select Run Chain.

  • Confirm that the 20210621_143720_Customer Master.csv file was downloaded and that column Last Update with a value 20210621_143720 was inserted as the first column in the data file.


Chain Error

Chains can error for a number of reasons. Most common failures occur due to a piece of information being incorrect - expired credentials, spelling errors, or selecting the wrong Variable. Simply copying and pasting the instructions of this guide may have resulted in this producing a Chain Execution error like below.

However, as this quick-start guide is for experienced users, we warned of a change to make when establishing the variables (Date & Time) to be used. Did you catch it? Hint, it's in Step 5.


📚 Topics covered in this exercise:

Command Groups

Conditionals

Dynamic Chain Variables

Iteration

Variable Transformation

Handlebars

SFTP

Tabular Transformation


Did this answer your question?