Import Error Handling

Import Error Handling in ServiceNow – Complete Guide for CAD Exam

Introduction

When working with external data in ServiceNow, imports do not always go smoothly. Data can be malformed, incomplete, or conflict with existing records. Understanding how ServiceNow handles import errors is critical for maintaining data integrity and ensuring reliable integrations. For the Certified Application Developer (CAD) exam, Import Error Handling is a key topic under the Working with External Data domain.

Why Is Import Error Handling Important?

Import operations bring data from external sources into ServiceNow staging tables and then transform that data into target tables. Without proper error handling:

- Data integrity is compromised: Bad or incomplete records can corrupt production tables.
- Business processes break: Downstream workflows, notifications, and automations may fail if they depend on clean data.
- Troubleshooting becomes difficult: Without logged errors, identifying the root cause of a failed import is time-consuming.
- Compliance risks increase: Inaccurate data can lead to audit failures and regulatory issues.

Proper error handling ensures that administrators and developers can identify, log, and resolve issues quickly while protecting the integrity of production data.

What Is Import Error Handling?

Import Error Handling refers to the mechanisms ServiceNow provides to detect, log, and manage errors that occur during the data import and transformation process. This includes:

- Import Set Errors: Errors logged during the loading of data into staging (import set) tables.
- Transform Map Errors: Errors that occur when data is mapped and moved from the staging table to the target table.
- Row-Level Error Tracking: Each row in an import set has a state that indicates whether it was successfully transformed, had errors, or was ignored.
- Error Log and Import Log: ServiceNow provides detailed logs that capture what went wrong, on which row, and why.

How Does Import Error Handling Work?

The import process in ServiceNow follows these general steps, and errors can occur at each stage:

1. Data Loading (Import Set Table)
Data is loaded into a staging table (import set table) from an external source such as a CSV file, JDBC connection, LDAP, REST, or other integration. Errors at this stage may include:
- File format issues (wrong delimiters, encoding problems)
- Column mismatches between the source file and the import set table
- Data truncation if values exceed column length

2. Transform Map Execution
A transform map defines how columns in the staging table map to fields in the target table. During transformation, common errors include:
- Coalesce failures: When the system cannot determine whether to insert or update a record because coalesce fields are empty or ambiguous.
- Mandatory field violations: Target table fields marked as mandatory may not have corresponding data.
- Data type mismatches: A string value mapped to a numeric or date field that cannot be converted.
- Reference field resolution failures: A value meant to resolve to a reference record (e.g., a user or group) does not match any existing record.
- Business rule or ACL conflicts: Server-side logic may reject or alter records during insert/update.

3. Row-Level States
After transformation, each row in the import set is assigned a state:
- Inserted – A new record was created in the target table.
- Updated – An existing record was updated.
- Ignored – The row was intentionally skipped (e.g., by an onBefore transform script or when no changes were detected).
- Error – The row encountered an error during transformation. The error message is stored on the import set row record.
- Pending – The row has not yet been processed.

4. Error Logging
Errors are captured in several places:
- Import Set Row (sys_import_set_row): Each row has a sys_import_state field and an error field that stores the error message.
- Import Log (sys_import_log): A log table that captures detailed messages for each import set run, including warnings and errors.
- System Log: General system logs may capture script errors that occur during transform scripts.

5. Transform Scripts for Error Handling
Developers can write onBefore and onAfter transform scripts to implement custom error handling logic:

- In an onBefore script, you can validate data and set ignore to true to skip problematic rows before they are inserted or updated.
- In an onAfter script, you can perform post-processing or additional validation after the record is saved.
- You can use error_message and status variables within transform scripts to set custom error messages and control the row state.
- Example: In an onBefore script, if a required field is empty, you can write:
if (source.u_email == '') { ignore = true; log.error('Skipping row: email is empty'); }

6. Handling Errors with the error_message and status Variables
Within transform scripts, the following variables are available:
- ignore: Set to true to skip the current row.
- error: Set to true to mark the current row as an error.
- error_message: Set a custom error message string for the row.
- status_message: Set a custom status message for informational purposes.

7. Robust Import Set API
When using the Import Set API (e.g., via REST), the response includes transformation results and error details for each row. This allows external systems to programmatically check for and respond to import errors.

Key Concepts to Remember

- Staging tables protect target tables: Data lands in a staging table first, and only clean data (or data that passes transform logic) moves to the target table.
- Coalesce fields determine whether a record is inserted or updated. If coalesce values are null or match multiple records, errors or unexpected behavior can result.
- Transform event scripts (onStart, onBefore, onAfter, onComplete, onForeignInsert) provide hooks for custom error handling and validation.
- The import set row record is the primary place to check for individual row errors and their messages.
- Error rows do not update/insert into the target table — the target table remains unaffected by rows marked as errors.

Best Practices for Import Error Handling

- Always validate data in onBefore transform scripts before allowing inserts/updates.
- Use meaningful error_message values so that troubleshooting is straightforward.
- Monitor the Import Log after every import run to catch warnings and errors early.
- Test import sets and transform maps in a sub-production instance before running in production.
- Use data source and scheduled import configurations carefully to avoid repeated errors in automated imports.
- Set up notifications or events to alert administrators when import errors exceed a threshold.

Exam Tips: Answering Questions on Import Error Handling

- Know the row states: Be able to identify and differentiate between Inserted, Updated, Ignored, Error, and Pending states. Exam questions often test whether you know what each state means.

- Understand the role of coalesce fields: Questions may describe a scenario where records are being duplicated or not updating. The answer often relates to improperly configured coalesce fields.

- Know where errors are logged: If a question asks where to find import error details, remember that the import set row record stores the error message, and the Import Log table provides a broader view of the import run.

- Understand onBefore vs. onAfter scripts: onBefore scripts run before the target record is saved and can set ignore = true to skip rows. onAfter scripts run after the record is saved. Questions may test when to use each.

- Remember the ignore and error variables: Setting ignore = true skips the row (state = Ignored), while setting error = true marks it as an error (state = Error). These are different outcomes — do not confuse them.

- Data type and reference resolution: If a question describes a scenario where a reference field fails to resolve, the answer typically involves either a choice map, a script-based lookup, or ensuring the referenced record exists before import.

- Staging table protection: Remember that the import set (staging) table acts as a buffer. Errors in the staging table do not corrupt the target table. This is a fundamental concept that may appear in conceptual questions.

- REST/API imports: Be aware that the Import Set API returns transformation results including errors in the response payload. Questions about integrations may reference this behavior.

- Read scenarios carefully: Many exam questions present a scenario with a specific error symptom (e.g., records not appearing in the target table, duplicate records). Trace the issue back to the import/transform process: Is it a coalesce issue? A missing mandatory field? A script error? Systematic thinking will help you choose the correct answer.

- Eliminate wrong answers: If an option suggests checking a table or log that is unrelated to imports (e.g., the email log or workflow context), it is likely incorrect. Focus on import-specific tables and mechanisms.

By mastering these concepts, you will be well-prepared to answer any CAD exam question related to Import Error Handling with confidence.