Deep Dive - Error Handling in Scripts

Article In Development...

The following methods are useful for verification and error handling in scripts

• Defines
• PreFlightCheck
• Postman
• PostNotification
• PostNotificationState
• GetNotifications
• StartErrorLog
• StopIfErrors
• Stop
• StopIf
• IF ELSE ENDIF
• RequiredFields
• RequiredFieldsFromTable
• ErrorIfNull
• ErrorIfDuplicate
• ErrorIfNullOrZero
• LogDataSetCount
• LogChanges
• LogWrite
• ETLSummaryFromTableEx
• CreateAuditTable

Overview of Scripting Errors

Script Errors fall into one or more of the following categories:

• API Errors - produced by scripting methods that returned from the server with a fail code
• Forced Errors - produced by scripting methods that are designed to error if specified criteria are met
• Logic Errors - produced when logical sequencing of script methods generates undesirable or incorrect results or behaviour
• Data Errors - produced when data is not as expected by the script designer

There is naturally some overlap between these groups.

API Errors

All Script methods produce a return code, indicating success or failure.   

Forced Errors

Logic errors

Data Errors

Scripting Methods

Defines

The defines method is used to set up script parameters.  Use the onError key to set up notifications that are to be sent in the event of a script error.   

{
  "method": "Defines",
  "description": "",
  "variables": [
    {
      "key": "%key%",
      "value": "value"
    }
  ],
  "onError": {
    "email": [
      ""
    ],
    "message": "script failed"
  },
  "project": "DJ_Load_Mar2023"
}

PreFlightCheck

{
  "method": "PreFlightCheck",
  "description": "VERIFY SCRIPTS AND PROJECTS",
  "directoriesExist": [],
  "filesExist": [],
  "filesOpenable": [],
  "project": "DJ_Load_2023"
}

Postman

Use Postman to set up notifications - 

{
  "method": "Postman",
  "subject": "Script Complete",
  "body": "This is an automated message from DataJet.\r\nYour script has completed.",
  "signature": "Message",
  "to": [
    ""
  ],
  "attach": [],
  "reportLinks": [],
  "project": "DJ_Load_Mar2023"
}

Post Notification

{
  "method": "PostNotification",
  "project": "",
  "notification": {}
}

Post Notification State

{
  "method": "PostNotificationState",
  "project": "",
  "nid": "",
  "state": ""
}

Get Notifications

{
  "method": "GetNotifications",
  "project": ""
}

Techniques for Verifying Results

Logging Commands

Start Error Log

Starts the logging process, outputting log information to file, and sending the log data to a specified email address when the script completes (data is emailed whether script completes with success or fail status).   Logging can be stopped using the StopErrorLog command. 

An optional Excel workbook can be included:

• "filename" - path and file to excel workbook.   Required.
• "append" - if true then logging data will be appended to the specified workbook.  Optional
• "variables" - the name of the worksheet in which to output resolved script variables (i.e., script parameters as set up in DEFINES statements). Optional.
• "info" - the name of the worksheet to contain the content of the error log (e.g., "filename").  This is content generated via the LogWrite, LogChanges and LogDatasetCount methods. Optional.
• "errors" - the worksheet to contain any Error log entries, such as ErrorIfDuplicates, ErrorIfNull. Optional.

{
  "method": "StartErrorLog",
  "filename": "ErrorLog.txt",
  "mailTo": [
    "load@djs.com"
  ],
  "workbook": {
    "filename": "%ETLAuditTable_All%",
    "append": true,
    "variables": "Variables",
    "info": "Info",
    "errors": "Errors"
  },
  "project": "DJ_Load_Mar2023"
}

Stop Error Log

Stops the Error Logging Process.  If running in the Script Editor window, outputs the content of the log to the Log Window and saves a copy of the log in the team export directory on the server. 

Stop If Errors

{
  "method": "StopIfErrors",
  "project": "DJ_Load_Mar2023"
}

Required Fields

Required Fields From Table

Error If Null

Used to verify that fields which must be fully populated have no null values.   If nulls are found, an error will be raised.

{
  "method": "ErrorIfAnyNull",
  "field": "ETL.djPRODUCTID",
  "project": "DJ_Load_Mar2023"
}

Error If Duplicate

Used to verify that fields which are required to be fully unique have no duplicates in them.   If duplicates are found, an error will be raised.

{
  "method": "ErrorIfDuplicate",
  "field": "DATA_RAW.djKEYFIELD",
  "project": "DJ_Load_Mar2023"
}

Error If Null or Zero

Reporting

Log DataSet Count

Outputs a dataset count to the Log File

{
  "method": "LogDatasetCount",
  "text": "File contains [{}] rows with duplicate product IDs.",
  "alwaysLog": true,
  "dataSet": {
    "logic": "or",
    "name": "DS_DuplicateRows 1",
    "strict": true,
    "set": [
      {
        "logic": "or",
        "stype": "FIELD",
        "entity": {
          "type": "field",
          "name": "ETL.DS_DuplicateRows",
          "table": "ETL",
          "valueFilter": []
        },
        "op": "=",
        "values": [
          "1"
        ]
      }
    ]
  },
  "project": "DJ_Load_Mar2023"
}

Log Changes

{
  "method": "LogChanges",
  "fromField": "table.field",
  "toField": "table.field",
  "project": "DJ_Load_Mar2023"
}

Conditional Processing & Script Control

IF ELSE ENDIF

[
  {
    "method": "IF",
    "condition": "",
    "project": "project"
  },
  {
    "method": "ELSE",
    "project": "project"
  },
  {
    "method": "ENDIF",
    "project": "project"
  }
]

STOP IF

[
  {
    "method": "STOPIF",
    "condition": "count([table.field],0)==0",
    "description": "stop if condition true",
    "project": "project"
  }
]

STOP

{
  "method": "Stop",
  "project": "DJ_Load_Mar2023"
}

Conditional behaviour within methods

ExportIntoTable

ignoreifnorows

skipifmissingsourcetable

CreateTableFromWorkbook

ignoreifnorows