Open database connection when needed

Overview

By default, Etlworks opens all database Connections referenced in the Flow before the Flow execution starts. It closes all Connections and coordinates distributed transactions when the Flow stops, successfully or with an error. 

It works perfectly well but creates a problem when the database Connection is parametrized using {tokens}. When the Flow starts, the values of the tokens (global variables) are not available yet, so the Flow fails before it even starts executing the transformations.

To solve this problem, Etlworks allows configuring a database Connection to be opened when needed. When needed means that the Flow defers opening the Connection to the moment when it is requested by the ETL Flow.  

Process

Here's how you can configure a database connection to be opened when needed:

Step 1. Open database Connection.

Step 2. Scroll to the Metadata section and enable Open connection when needed.

Limitations

The Connection configured with {tokens} is not going to work in Explorer, Mapping, and test transformation, regardless of the state of Open connection when needed.

Using Tokens in Database Connections with Multiple Flows or Transformations

When a database connection is configured with {tokens} in the URL, username, password, or other fields, it must be used with the “Open connection when needed” option enabled. This ensures that tokens are resolved dynamically at runtime, allowing the same connection to be reused in different flows or nested tasks with different values.

However, once the connection is opened (even with “Open connection when needed” enabled), it becomes shared and available to other flows, transformations and tasks, including those in nested flows and flows with multiple source-to-destination transformations . To avoid conflicts and ensure each flow uses the correct database configuration:

• Enable “Create new database connection for destination” if you are loading data into different target databases using the same tokenized connection.

• Enable “Create new database connection for source” if you are extracting data from different source databases using the same tokenized connection.

These options ensure a new connection is created for each flow execution, allowing tokens to be resolved independently and avoiding cross-flow/transformations contamination.