Handling exceptions in the flows and in the transformations

Ignoring 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, in a typical scenario the flow would throw an exception. 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 the mapping button 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.

Capturing 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 can return an empty string if the error was not generated by the task.

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());
}

Sending 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}. 

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 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:

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

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