Debug a database loop

Problem

When executing Flows in a database loop, it is possible that the fields in the SQL result set, which drive the loop, will be null or empty. If that happens, Etlworks will not be able to replace the {tokens} with actual values. As a result, the Flow in the loop will be executed with an error.

For example:

If the Flow to be executed in the loop uses a source Connection with a tokenized URL: http://host.com?startDate={startDate}&endDate={endDate}.

The driving SQL is: select startDate, endDate from config.

But for some records, the value of the startDate field is null, while for others it has an actual non-empty value, for example, yesterday.

As a result, when Etlworks tries to replace the token {startDate} in the URL, in order to reach the source Connection, it will not be able to find a non-empty value, so the URL will remain unchanged: http://host.com?startDate={startDate}. Most likely, this will cause the Flow to fail.

Solution

When Etlworks detects that some of the values in the variable-value pairs, which are used to substitute {variable} tokens are null or empty, it adds the following line to the debug log:

WARNING: scenario 'scenario name' will be executed with an empty value for the variable {variable name}.

View debug log from the Flows window.

View debug log from the Audit Trail window.