Parameterization is a way to dynamically configure Connections and transformations based on input parameters. The input parameters can be Global Variables and Flow variables.

Types of parametrization

• Parameterization of the Connections 
• Parameterization of transformations 
• Parameterization of file operations
• Parameterization of SQL queries
• Dynamic connections
• Dynamic flows

Global Variables

Global variables are key/value pairs, where both the key and value are strings. Developers can reference a global variable in connections and transformations by using the syntax {variable_name}. Global variables are accessible throughout the entire nested flow hierarchy, meaning they become available to all flows within that hierarchy immediately after they are set.

Common Use Cases for Global Variables

Global variables can be used in various scenarios to make your workflows more dynamic and flexible. Common use cases include:

• Parameterization of Connections: Use global variables to dynamically set connection parameters such as URLs, usernames, or passwords.
• Parameterization of Transformations: Global variables can be used to customize transformation source and destination based on dynamic values.
• Parameterization of File Operations: Apply global variables in file operations (e.g., file paths, file names) to manage files more efficiently.
• Parameterization of SQL Queries: Incorporate global variables into SQL queries to enable dynamic query conditions and values.

Set global variables

Variables set programmatically or using configuration:

• Set global variables in a Script : Define and assign values to global variables using a script.
• Database Loop Parameters : Set variables based on the results of a database query.
• File Loop Parameters : Loop through the files matching the wildcard name and set variables to file name.
• Flow variables set at the flow level : Set specific variables within the flow configuration.
• Variables set when executing flow manually : When running a flow manually, variables can be entered directly before the flow starts.
• Variables set when configuring the schedule : When setting up a scheduled flow, variables can be defined as part of the schedule configuration.
• Variables set when configuring the flow to be executed by Integration Agent : If the flow is executed by an Integration Agent, variables can be set in the agent’s flow configuration.
• URL parameters in call-flow-by-name API :Pass variables through URL parameters when triggering a flow by name.
• Payload in call-flow-by-ID API : Provide variables via the payload when triggering a flow by ID.
• User-defined API URL parameters : Use parameters defined in your custom API endpoints.
• HTTP Preprocessor : Set variables dynamically using an HTTP preprocessor script.

These global variables are automatically set when executing flows (no scripting or configuration required):

• Source filename
• Destination filename
• Tenant and user name

Reference Global Variables

To reference a global variable, use the following syntax: {variable name}.

The value of the variable must not be empty. When Etlworks replaces the {variable name} with an actual value, it ignores null and empty values.

Examples:

Input: http://host.com?startDate={stateDate}
Variable-value: startDate-null
Output: http://host.com?startDate={stateDate}

Input: http://host.com?startDate={stateDate}
Variable-value: startDate-""
Output: http://host.com?startDate=

Input: http://host.com?startDate={stateDate}
Variable-value: startDate-today
Output: http://host.com?startDate=today

Flow Variables

Flow variables are key/value pairs, where both the key and value are strings. Developers can reference flow variables in SQL statements, JavaScript and Python scripts using the syntax {variable_name}.

Unlike Global variables, Flow variables are individually set for each flow within a nested flow hierarchy. However, there is one exception: when the main (parent) flow in a nested hierarchy starts, all flows within the hierarchy inherit the value of any flow variable set in the main flow.

Common Use Cases for Flow Variables

Parameterizing SQL Statements

Flow variables are often used to dynamically parameterize SQL queries. Instead of hardcoding values, you can use flow variables to inject dynamic data into your SQL statements, such as filter conditions, limits, or values for insert and update operations. This makes SQL queries flexible and adaptable to varying inputs during flow execution.

Example:

SELECT * FROM orders WHERE order_date >= '{start_date}' AND order_date <= '{end_date}'

Parameterizing Scripts

Flow variables can be embedded into scripts across different scripting languages supported by Etlworks, allowing you to customize and control flow behavior programmatically. These variables enable dynamic adjustments in your scripts depending on flow parameters or conditions at runtime.

Example JavaScript:

var recordCount = {RECORD_COUNT};
if (recordCount > {A_FEW}) {
    // code
}

Example Python:

recordCount = {RECORD_COUNT};
if recordCount > {A_FEW}) 
    # code

Example bash:

record_count=${RECORD_COUNT}
if [ "$record_count" -gt {A_FEW} ]; then
   # code
fi

Set Flow Variables

Here are the different ways to set and use flow variables:

• Flow variables are passed as URL parameters or URL variables to the user-created API endpoints.
• Flow variables are passed as URL parameters to the run Flow by name API.
• Flow variables are passed as a payload to the run Flow by ID API.
• Flow variables can also be defined when creating nested Flows.
• Flow variables can be set and accessed programmatically.
• Flow variables set as database loop parameters.
• Flow variables set as file loop parameters.
• Flow variable set when executing flow manually.
• Flow variable set when configuring the schedule.
• Flow variable set when configuring the flow to be executed by Integration Agent.