Error-handling, or event-handling, is an essential practice for any data integration project. Determining the events and “handling the encountered events” is a crucial design aspect in a robust design. Precise, detailed, and meaningful notifications enable efficient analysis of the captured events by the business or IT teams.

In this post, we describe best practices for handling unexpected and expected events within Jitterbit. Included are logging, validation, and notification methods depending upon severity.

In a later post, we discuss, along with On Failure Operations, techniques for threshold checks and restart-ability. These will ease maintenance tasks.

Jitterbit Logging

With WriteToOperationLog(), informational field values should be logged per Operation. This logging is helpful for analysis and troubleshooting.

In particular, you want to log field values that are meaningful to business and IT teams. Jitterbit has a powerful log search capability. By logging values significant to business users, you can more quickly identify any relevant logs associated with a prior event of interest. For example, log the unique identifier, and the record name. This log entry allows you to search for the name rather than wasting time looking up its unique identifier before your search.

Event Types and Severities

Jitterbit events might be classified with expectations as follows:

Type Description Severity
Unexpected Whether deliberate, caught and/or data-driven; operation usually terminated Emergency, Alert, Critical or Error
Unexpected Jitterbit warnings, desired warnings and any Jitterbit error not considered severe Warning or Notice
Expected Other events requiring a log entry or notification Notice, Informational or Debug
Expected Data validations Warning, Notice, Informational or Debug

Data validations are unique processes in data integrations. We examine data validation within Jitterbit further below.

Unexpected Events

Most unexpected errors are trapped as Jitterbit Operation Errors. Operation errors, by default, will immediately stop an operation chain. If understood as severe and catastrophic, these errors are best handled by On Failure Operations. Another method to trap and handle errors is within a Script using:

If( !RunOperation("..."),...);

Here, one can assess the $jitterbit.operation.last_error variable and use the RaiseError function to end the operation. Note, with RaiseError; you can cause a Jitterbit error at any time within a Script. The term “Raise Error” is used to evoke a Jitterbit Operation error and an event that likely needs handling.

There is one unexpected error that should always be handled. These involve the Jitterbit database functions. Databases can be ornery applications that can issue errors for data, syntax, or other issues. When using functions, Jitterbit will log any error, but it is not considered a fatal Operation error so the operation will continue (as best it can). The proper way to trap a potential database error is as follows (for DBLookup())

error=Eval(
  value=DBLookup("...","...")
,
  RaiseError("DBLookup failed! "+error)
)

The thorough use of Eval() for many Jitterbit functions is a good practice. Although it could be taken too far, each use of a Jitterbit function is a potential unexpected error and should be assessed accordingly.

Expected Events

Expected errors are identified and logged during a data validation/quality processing phase. Unlike most unexpected errors, when conducting data validations, you may not want the Operation chain to halt when a record fails. The validations should continue with the incoming data. If many validations are done on a record, all the validations should be done to ensure all the errors are captured.

Data Validation

When data is available in the row-column format, Jitterbit provides a rich and powerful data validation process. The File Format component includes options to confirm each row or field.
The row/record is skipped but, depending upon target requirements, a default value can be applied, or the field could be ignored. One can skip the row or abort the Operation (Raise Error) if the least number of column values are not available. Also, per field, one can use some general validation rules and more specific content validation rules. This feature includes applying defaults or Scripts for handling events.

If an Operation’s Transformation uses a File Format as a Source, the Operation’s Options provides for handling. For example, for emailing or storing valid or invalid records. Once enabled, you can record invalid records in a dedicated Target file. Errors go in a final/last column then all can be viewed with a spreadsheet editor. Depending upon your data validation requirements, it may be worth transforming XML data into a flat file for validation.

Tracking an error count and acting upon an event or error threshold check can be another need. Below, we discuss working upon an event and, within the next installment, we’ll discuss threshold checks.

Event Notification Methods

A good practice is to centralize the event/error handling and logging design where appropriate. This has the advantage of allowing adaptations (think Datadog or Splunk) to be made in one place. Desirable features include:

  • Configurable details available when needed using a global log-level variable value
  • Event severity configuration
  • Optional immediate email of the event

A formal, scripted condition:

If( !RunOperation("...",
  ...;
  RunScript("...");
  ...;
);

In practice, lengthy scripting of data workflow is NOT a good strategy. The long term maintenance can get arduous. To process the above event handling requirements, an On Failure Operation can call a parameterized script. The example script below (“Event”) provides broad parameters and event severity values.

(You may benefit from the walkthrough post). The relevant email scripts are discussed in the HTML Email post.

// Event is a utility script to log an event -- it wraps WriteToOperationLog() and a good
// replacement for using WriteToOperationLog() directly. It provides parameters to control and
// get alerts to “events” in the Operation Log.
//   In particular, it offers the following features:
//     o Allows use of "extensive logging" with controllable runtime verbosity using $LOG.level
//     o Optional alternative notification or logging
//       o Immediate email notification upon event
//       o Configurable fields to record categories like "type" and "category"
//       o Jitterbit File Target to write appendable logs to a file
//     o A standardized prefix on each logged event (Operation name, type and category)
//   Uses Script "Initialize LOG.severity to declare your own log level hierarchy
//   For example, using default $LOG.severity array (see Script Initialize Log.severity):
//     LOG.level        Message Codes Seen
//     Debug            [0..7] (ALL)
//     Informational    [1..7] (All but "Debug")
//     Notice           [2..7] (..
//     Warning          [3..7]
//     Error            [4..7]
//     Critical         [5..7]
//     Alert            [6..7] ..)
//     Emergency        7      (See only Event messages with event code 7)
 
//   Note, arguments cannot be skipped; use "" for unused arguments "between used arguements".
//   That is, trailing empty arguments can be ignored and not provided
 
_event_code = Int(_1); //[0..?]
_event_type = _2; //anything really, typically the severity corresponding to the _event_code
_event_msg = IfEmpty(_3, //optional 
               IfEmpty(GetLastError(),
                 // within On Failure Op or after a failed RunOperation()
                 IfEmpty($jittrbit.operation.previous_error, 
                   "NO EVENT MESSAGE OR PREVIOUS VALUE/ERROR AVAILABLE")
             ));
_send_event = Bool(IfEmpty(_4, false)); //optional, needs configuration below
_event_category = _5; //optional, perhaps for future event sorting
//file = IfEmpty(_6,$LOG.filename); //Target filename global variable name; configuration below
//If(file && RegExMatch(file,"(^[0-9a-zA-Z\^\&\'\@\{\}\[\]\,\$\=\!\-\#\(\)\.\%\+\~\_ ]+$)")!=1,
//  RaiseError("Invalid filename value chosen for Event logging to file: "+Quote(file));
//);
RunScript("<TAG>Scripts/Utilities/Initialize Log.severity Array</TAG>");
 
helperArr = Array(); i=0; While(i<Length($LOG.severity[_event_code]), Set(helperArr,i,-1); i++);
 
$jitterbit.operation.name=IfEmpty($jitterbit.operation.name,"NO OPERATION NAME AVAILABLE");
 
severityLevel = FindValue($LOG.level, $LOG.severity[_event_code], helperArr);
If(!IsNull(severityLevel), // level is within desired $LOG.level
  type = msg = $jitterbit.operation.name+": "+ _event_type + " :: "; // Event type
  msg += If(!IsNull(_event_category),_event_category+" :: "); // Event category
  msg += _event_msg + "\n"; // Operation message
  $EVENT.msg=msg; outcome=""; //initialize message and outcome
 
  $EVENT.env = GetEnvironmentName()
    +' '+ToUpper($LOG.severity[_event_code][Length($LOG.severity[_event_code])-severityLevel-1]);
  $Event.op = IfEmpty($Event.op,$jitterbit.operation.name);
 
  If(Bool($EMAIL.text)==True,
    If(_send_event
      && (outcome=SendEmailMessage("<TAG>Email Messages/TEXT Event</TAG>")) != "",
        $EVENT.bp = RunScript("<TAG>Scripts/Utilities/Log Environment</TAG>");
        $EVENT.msg.email = $EVENT.msg  +'\n'+ $%EVENT.host;
        outcome="Email Notification of EVENT/ERROR (" + type + ") failed: " + outcome
        + "\nEmail Message Body:\n";
    );
  ,
    If(_send_event
      && (outcome=SendEmailMessage("<TAG>Email Messages/HTML Event</TAG>")) != "",
      $EVENT.bp = RunScript("<TAG>Scripts/Utilities/Build EVENT.bp</TAG>");
      $EVENT.msg.email = RegExReplace($EVENT.msg,'\r?\n','<br>')  +'<br><br>'+ $%EVENT.host;
      outcome="Email Notification of EVENT/ERROR (" + type + ") failed: " + outcome
      + "\nEmail Message Body:\n";
    );
  );
 
//  If(!IsNull(file) && Length(file)>0, 
//    WriteFile("<TAG>Targets/Job Logs/Event Log</TAG>", outcome+$EVENT.msg)
//  );
  If(_send_event,
    WriteToOperationLog("Email sent/outcome..\n"+outcome+$EVENT.msg);
  ,
    WriteToOperationLog($EVENT.msg)
  );
);

It’s worth mentioning, all logging could use the above script; one can script all event logging needs. You can control their visibility with the $LOG.level variable and actions like Email alerts.
If the above script is titled “Event”, use this within other Scripts like:

If(!RunOperation(…),
  event=RunScript("<TAG>Scripts/Handlers/Event</TAG>", 0, "Emergency Type", “”, false, "FATAL");
  RaiseError(event) //catastrophic, abort (or catch) the operation chain
);

Summary

The above practices with Jitterbit empower an integration design that is flexible and robust. Best practices include:

  • Design error-handling capabilities for both the unexpected and expected errors.
  • Cut lengthy script-driven workflow — use On Failure Operations.
  • Centralize the event/error handling.

Try the above Event Script, with each invocation below. After the execution of each, view the Operation Log.

// see results using different $LOG.level values
$LOG.level="Debug"; WriteToOperationLog("\nLOG.LEVEL:"+$LOG.level); 
RunScript("<TAG>Scripts/Handlers/Event</TAG>",0,"Emergency type logged using $LOG.level="
  +$LOG.level,"System is unusable.",false,"FATAL");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",1,"Alert type logged using $LOG.level="
  +$LOG.level,"Should be corrected immediately.",false,"ALERT");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",2,"Critical type logged using $LOG.level="
  +$LOG.level,"Critical conditions.",false,"CRITICAL");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",3,"Error type logged using $LOG.level="
  +$LOG.level,"Error conditions.",false,"ERROR");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",4,"Warning type logged using $LOG.level="
  +$LOG.level,"May show that an error will occur if action is not taken.",false,"WARN");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",5,"Notice type logged using $LOG.level="
  +$LOG.level,"Events that are unusual, but not error conditions.",false,"NOTICE");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",6,"Informational type logged using $LOG.level="
  +$LOG.level,"Normal operational messages that needs no action.",false,"INFO");
RunScript("<TAG>Scripts/Handlers/Event</TAG>",7,"Debug type logged using $LOG.level="
  +$LOG.level,"Information useful to developers for debugging the application.",false,"DEBUG/ALL");