Handle exceptions in the Flows and in the transformations

Ignore Exceptions

In some cases, it makes sense to ignore all or specific exceptions when performing a transformation or executing a Flow. For example, if you are running a Flow that reads the files in a loop and some of the files do not exist anymore, the Flow would throw an exception in a typical scenario. In order to avoid such an error, you can configure the transformation to ignore the file not found exception.

To configure the transformation to ignore an exception, click MAPPING for that transformation, select the On Exception tab, and select Ignore for the On Exception field.

Optionally, enter part of the exception string, which matches with an actual exception, for example: file not found.

You can enter multiple semicolon (;) separated exception strings, for example, file not found;file is a directory.

Execute JavaScript in case of any error

You can configure a JavaScript program that will be executed in case of any error regardless of whether this error is ignored by the flow or not.   

Return value

If the program returns an integer value it overrides the selected On Exception action: Raise = 1 and Ignore = 2. 

Raise exception

exception hander code;

value = 1;

Ignore exception

exception hander code;

value = 2;

Available variables

The following objects can be referenced by name from JavaScript code:

Object name	Class name / JavaDoc	Import
etlConfig	com.toolsverse.etl.core.config.EtlConfig	importPackage(com.toolsverse.etl.core.config);
scenario	com.toolsverse.etl.core.engine.Scenario	importPackage(com.toolsverse.etl.core.engine);
exception	java.lang.Exception The current exception	importPackage(java.lang);
row	current 0-based row number
code	the SQL or script that caused the error
handler	Source, Destination, or Task	importPackage(com.toolsverse.etl.core.engine);
connection	SQL connection	importPackage(java.sql);
action	configured On Exception action: Raise = 1 and Ignore = 2

Capture the last exception

The last exception is available from the JavaScript using etlConfig.getLastException(). The returned value, if not null, is an instance of the EtlException class (a subclass of Java Exception class), which has the following useful methods:

• getMessage(): returns the error message.
• getScenarioName(): returns the name of the ETL scenario (Flow).
• getBlockName(): returns the name of the source or destination (as in the source-to-destination transformation), which was the originator of the error.
• getTaskName(): returns the name of the task. This method could return an empty string if the task did not generate the error.

The code fragment below demonstrates how to access the exception and save it in the status table in the database:

importPackage(java.lang);
importPackage(java.util);
importPackage(com.toolsverse.config);
importPackage(com.toolsverse.util);
importPackage(com.toolsverse.etl.sql.util);

// get the last exception
var ex = etlConfig.getLastException();

// reset the last exception
etlConfig.setLastException(null);

if (ex != null) {
 // get the exception string 
 var message = ex.getMessage();

 if (Utils.isNothing(message)) {
     message = Utils.getStackTraceAsString(ex);
 }
 
 // get the thread-safe global variables
 var props = SystemConfig.instance().getContextProperties();
 
 // get the connection to the statu database
 var connection = etlConfig.getConnectionFactory().getConnection("your name");
 
 var sql = "insert into feeds_status (error, finished) values (?,?)";

 // execute sql with two variables: message and current timestamp
 SqlUtils.executeSql(connection, sql, 1, null, message, new java.util.Date());
}

Send notifications by email about failed transformations

When the transformation or Flow is configured to ignore the exception, it is possible to collect all the ignored errors and send the notification by email to the designated recipients.

Step 1. Create Send Email Flow as explained here.

Step 2. Tokenize the Message field: {handled_exceptions}.

Step 3. Create JavaScript Flow with the following code. Note the global variable name set in the last line. It must be the same as the name of the token.

importPackage(com.toolsverse.config);

var allExceptions = etlConfig.getValue('all_exceptions');

var exceptions = "";

if (allExceptions != null && !allExceptions.isEmpty()) {
    for each (var element in allExceptions) {
      var etlException = element.getKey();
 
      var error = Utils.getMessage(etlException, null);
 
      var flow = etlException.getScenarioName();
 
      var block = etlException.getBlockName();
 
      var task = etlException.getTaskName();

      var reason = etlException.getReason();  

      var isWarning = etlException.isWarning();
 
      var message = 'Error: ' + error + ". Flow: " + flow 
           + ". ETL block: " + block + ". Task: " + task;
 
      exceptions = exceptions + element.getValue() + ' - ' + message 
           + Utils.NEWLINE;
    }  
} else {
    exceptions = 'No exceptions';
}

SystemConfig.instance().getProperties().put('handled_exceptions', exceptions);

Step 4. Configure transformations or Flows to ignore specific exception(s) as explained above.

Step 5. Add Flows all Flows to the main nested Flow. 

Step 6. Configure Send Email Flow to be executed conditionally by adding the following JavaScript condition:

value = allExceptions != null && !allExceptions.isEmpty();

Filter out warnings and exceptions based on a reason

Some of the exceptions recorded in all_exceptions variable (see above), are not real exceptions but rather warnings. For example, when the transformation is configured to automatically adjust the payload based on the destination's metadata (columns and data types), the system still records the metadata mismatch between the source and the destination as an exception.

It is done on purpose, so the developer has an option to configure a notification (see above) that will be sent each time the source and the destination are not the same in terms of the number of columns.

There are two attributes that can be used to filter out warnings:

• etlException.isWanting(): a boolean set to true for all metadata mismatch exceptions (it is false in all other cases).
• etlException.getReason(): currently, there are two recorded reasons:
  • "create": the columns are different in the source and in the destination, and the transformation is configured to recreate the target table.
  • "alter": the columns are different in the source and in the destination, and the transformation is configured to alter the target table.

To filter out all warnings, modify the JavaScript code which parses the errors:

importPackage(com.toolsverse.config);

var allExceptions = etlConfig.getValue('all_exceptions');

var exceptions = "";

if (allExceptions != null && !allExceptions.isEmpty()) {
    for each (var element in allExceptions) {
      var etlException = element.getKey();

      if (etlException.isWarning()) {
          continue;  
      }
 
      var error = Utils.getMessage(etlException, null);
 
      var flow = etlException.getScenarioName();
 
      var block = etlException.getBlockName();
 
      var task = etlException.getTaskName();

      var reason = etlException.getReason(); 

      var isWarning = etlException.isWarning();
 
      var message = 'Error: ' + error + ". Flow: " + flow 
            + ". ETL block: " + block + ". Task: " + task;
 
      exceptions = exceptions + element.getValue() + ' - ' + message 
            + Utils.NEWLINE;
    }  
}

if (Utils.isNothing(exceptions)) {
    exceptions = 'No exceptions';
}

SystemConfig.instance().getProperties().put('handled_exceptions', exceptions);