"Error occurred during row insert" while importing JSON data using two transform maps

[beycos]

Hi everyone,

I'm facing an issue while importing data from a JSON file using a Data Source in ServiceNow. There are two transform maps involved in the process:

• 1 – uses a field map, and the name field has COALESCE = true

• 2 – a script-only transform map

Use case:

I'm trying to import two records that have the same name — one active, and one that was previously deleted . The script in 2 is designed to allow this and seems to handle the logic correctly.

Issue:

Despite the script seemingly working, I consistently receive the following error during the import:

"Error occurred during row insert"

This is my script

(function runTransformScript(source, map, log, target) {
var recordName = source.record_name; 
var deletionDate = source.date_deleted; 
var updated = false;

// 1. Find record with same name AND deletion date
var existing = new GlideRecord('my_table_name'); 
existing.addQuery('name', recordName);
existing.addQuery('date_deleted', deletionDate);
existing.query();

if (existing.next()) {
log.info('Found matching record, updating: ' + existing.sys_id);

existing.status = source.status;
existing.company = source.company;
existing.code = source.code;
existing.description = source.description;
// Update other fields as needed

existing.update();
updated = true;
}

// 2. If not found, find active record (no deletion date) with same name
if (!updated) {
var active = new GlideRecord('my_table_name');
active.addQuery('name', recordName);
active.addQuery('date_deleted', ''); // Active records only (no deletion date)
active.query();

if (active.next()) {
log.info('Found active record with same name, updating: ' + active.sys_id);

active.status = source.status;
active.company = source.company;
active.code = source.code;
active.description = source.description;
active.date_deleted = source.date_deleted; // Mark as deleted if applicable

active.update();
updated = true;
}
}

// 3. If still not updated, insert new record
if (!updated) {
var newRec = new GlideRecord('my_table_name');
newRec.initialize();

newRec.name = recordName;
newRec.status = source.status;
newRec.company = source.company;
newRec.code = source.code;
newRec.description = source.description;
newRec.date_deleted = source.date_deleted;

var newSysId = newRec.insert();
if (newSysId) {
log.info('Inserted new record: ' + newSysId);
}
}

// Prevent default insert/update by transform map
target.setAbortAction(true);
})(source, map, log, target);

Could you please help me understand why this error occurs, especially since the script is allowing the insertion? 

Thanks in advance for your help!

[ayushraj763]

1. Reviewed Script Logic

• The script checks for an exact match (name + date_deleted)
• If not found, it checks for active record (name + no date_deleted)
• If still not found, it inserts a new record
• Ends with target.setAbortAction(true) to prevent default insert/update
1. Suspected Coalesce Conflict
• Since Map 1 has COALESCE = true, it may be trying to update a record before Map 2 runs
• This could cause a conflict when two records share the same name but differ in date_deleted
2. Tested with Map 2 Only

Disabled Map 1 temporarily

1. Ran import with only the script map active
2. Result: No error — insert/update worked as expected

1. Validated Field Values

• Checked that all incoming values (e.g., status, company, code) match expected formats
• Ensured no invalid references or choice mismatches

1. Checked ACLs and Permissions

• Verified that the import user has insert/update rights on the target table
• No ACLs blocking the operation

1. Added Logging and Try-Catch

• Wrapped insert() in a try-catch block to catch any hidden exceptions
• Added detailed logs to trace each step

[ayushraj763]

Troubleshooting Steps

1. Reviewed Script Logic

• The script checks for an exact match (name + date_deleted)
• If not found, it checks for active record (name + no date_deleted)
• If still not found, it inserts a new record
• Ends with target.setAbortAction(true) to prevent default insert/update

1. Suspected Coalesce Conflict

• Map 1 has COALESCE = true, which may try to update a record before Map 2 runs
• This could cause a conflict when two records share the same name but differ in date_deleted

1. Tested with Map 2 Only

• Disabled Map 1 temporarily
• Ran import with only the script map active
• Result: No error — insert/update worked as expected

1. Validated Field Values

• Checked that all incoming values (e.g., status, company, code) match expected formats
• Ensured no invalid references or choice mismatches

1. Checked ACLs and Permissions

• Verified that the import user has insert/update rights on the target table
• No ACLs blocking the operation

1. Added Logging and Try-Catch

• Wrapped insert() in a try-catch block to catch any hidden exceptions
• Added detailed logs to trace each step

[beycos]

I suspect the issue might actually be related to this part of the script:

// Prevent default insert/update behavior of the transform map

    target.setAbortAction(true);

When I comment out this line, the error no longer appears, but empty default records are being created by the transform map.

[beycos]

I’m using a Transform Map with a scripted transform to manually handle insert/update operations in ServiceNow. In my script, I use target.setAbortAction(true); to prevent an automatic insert/update since I’m doing these operations manually via GlideRecord. Although my manual insert seems to work correctly and the data is inserted, the transform map still throws this error: "Error occurred during row insert."

 Are there any best practices when using target.setAbortAction(true); in transform scripts?