ServiceNow Import Sets and Transform Maps: A Practical Guide

External Data Source
    → Import Set Table (staging area)
    → Transform Map (mapping + transformation)
    → Target Table (incident, cmdb_ci, etc.)

// Example: Importing user data
// Coalesce on: email (unique per user)
// If email matches existing user → update
// If email not found → create

// Example: Importing CIs
// Coalesce on: serial_number
// If serial_number matches existing CI → update
// If serial_number not found → create CI

// onBefore script: runs before each record transforms
// Use to: skip invalid records, set defaults

// Skip records with no email address
if (!source.email) {
    ignore = true;
    return;
}

// Clean phone number format
source.phone = source.phone
    .replace(/[^0-9+]/g, '');

// onAfter script: runs after each record transforms
// Use to: create related records, trigger workflows

// Create a task after each user is created
if (action === 'insert') {
    var task = new GlideRecord('task');
    task.short_description = 'Welcome ' + target.first_name;
    task.assigned_to = target.sys_id;
    task.insert();
}

Transform Map coalesce — preventing duplicate records

Coalesce is the mechanism that tells ServiceNow how to identify existing records for update rather than always inserting new ones. Set the Coalesce field on a Transform Map field mapping to true for the field(s) that uniquely identify the record in the target table — typically an employee number, email address, or external system ID. On each import row, ServiceNow queries the target table for a record where the coalesce field matches the import row's value. If found, it updates that record; if not found, it inserts a new one. Without coalesce, every import creates duplicate records rather than updating existing ones — a common mistake that produces data quality problems in recurring imports.

Import Set performance for large files

For files with more than 10,000 rows, enable background processing to avoid UI timeouts during the import run. Navigate to the Import Set record, check the "Background" option before running the transform. Monitor progress via the Import Set Worker related list. For very large imports (100,000+ rows), ServiceNow recommends splitting files into smaller batches and processing them sequentially rather than processing a single massive file, which can cause instance resource contention during the transform run. The MID Server can also be used to retrieve and stage large files before import, reducing the load on the main instance during file processing.

Related: Import Sets guide · Scheduled Jobs · MID Server · Update Sets

Transform Map Scripts: Before, OnBefore, OnAfter

Transform Maps support scripted logic at three execution points. The onBefore script runs before each row transformation and is where you implement record skipping, data lookup, and pre-processing logic. Call ignore = true in an onBefore script to skip importing the current row without creating an error record — useful for filtering out rows that match conditions you want to exclude (e.g., records with a specific status in the source system that have no equivalent in ServiceNow). The onAfter script runs after each row transformation on the created or updated record, enabling post-processing like setting related records or triggering notifications. The onComplete script runs once after all rows are processed, ideal for sending a summary email or updating an integration status record with import statistics.

Coalesce Fields and Deduplication

Coalesce is the mechanism that prevents duplicate records on repeated imports. When a field mapping has coalesce enabled, ServiceNow checks whether an existing record in the target table matches the coalesce field value before creating a new record. If a match is found, the existing record is updated rather than a new one created. Without coalesce, every import run creates new records, rapidly filling the target table with duplicates. For most integrations, coalesce on a unique identifier from the source system (an external ID, employee number, or ticket reference) is the right choice. You can coalesce on multiple fields simultaneously — the combination of first name and last name as a coalesce pair, for example, though this is less reliable than a true unique identifier. The relationship between coalesce and the CMDB's IRE is important to understand for CMDB-targeted imports.

Handling Relationships in Transform Maps

When the target table has reference fields (like assigned_to on Incident, which references sys_user), the Transform Map needs to resolve the incoming value to a sys_id. ServiceNow's reference lookup handles this automatically for display value matching — if the source data contains a user's name or email, the field mapping can be configured to look up the matching sys_user record. For complex lookups or when the source data uses a non-standard identifier, use a field mapping script: var user = new GlideRecord('sys_user'); user.addQuery('employee_number', source.employee_id); user.query(); if (user.next()) { answer = user.sys_id; }. The encoded query guide covers the query patterns used in these lookup scripts.

Error Handling and Import Set Cleanup

Failed transformation rows are logged in the Import Set Run with an error state and message. Import Sets retain their staging data in the import table after transformation — the raw imported rows persist until cleaned up. For high-volume recurring integrations, implement an automated cleanup job that purges import table records older than a defined retention window, otherwise the import tables grow without bound and eventually affect query performance. The cleanup can be a Scheduled Job running a simple GlideRecord delete query against the import table filtered by sys_created_on. For imports that fail entirely (transform map errors, connection failures), implement alerting on the Import Set Run table so operations teams are notified promptly rather than discovering missed imports days later.

Transform Map Testing Strategy

Test Transform Maps with a representative sample before running production loads. The Import Set module includes a "Test" option that processes a single staging row through the transform map and shows the outcome without committing the result — this is the right starting point for any new transform configuration. After single-row testing, run a small batch (50-100 records) and review the results in the target table and the Import Set Run log before processing the full dataset. Pay specific attention to records that created new target records when they should have updated existing ones (coalesce misconfiguration) and records with unexpected null values in required fields (transform logic gaps). The investment in staged testing returns many times over in avoided data cleanup work when a transform error is caught at 100 records rather than 50,000.

Large Volume Import Performance

Import Sets processing tens of thousands of rows can run for hours if the Transform Map script logic is expensive per row. The primary performance lever is query efficiency in field mapping scripts — a lookup query in a field mapping that runs for every row creates N queries for an N-row import. Where lookups are needed, cache results: build a JavaScript object at the start of the onBefore script that maps common lookup values to their sys_ids, then use the cached object for the per-row lookup instead of running a GlideRecord query for each row. For imports where many rows are duplicates of already-imported data, add a pre-filter in the onBefore script to skip rows that have not changed since the last import, reducing the effective processing volume. These two optimisations together can reduce a 6-hour import to under 30 minutes for typical enterprise data volumes.