Parameterization in Etlworks allows you to dynamically configure connections, file operations, transformations, and SQL queries using input variables. These variables can be either Global Variables or 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 (both strings) that can be referenced across the entire nested flow hierarchy using the syntax {variable_name}. Once set, they’re immediately available to all child flows.

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: Dynamically define URLs, usernames, and passwords
• Parameterization of Transformations: Set source/destination dynamically
• Parameterization of File Operations: Parameterize file paths and names
• Parameterization of SQL Queries: Inject variable values into queries

How to Set Global Variables

Global variables can be set using:

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

Automatically Set Variables

• Source filename
• Destination filename
• Tenant and user name

Reference Global Variables

Use {variable_name} in any configuration field.

If the variable is null, it is not replaced. If it is an empty string, the result will include an empty value.

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 also key/value pairs and use the same {variable_name} syntax, but they are scoped to a specific flow. Child flows inherit variables from the parent, but not from sibling or unrelated flows.

Common Use Cases for Flow Variables

SQL Parameterization

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

How to 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.