CSA Guide

Mastering Transform Maps and Import Sets

Introduction

Every enterprise ServiceNow implementation eventually needs to ingest data from external systems—migrating legacy tickets, onboarding CMDB data from discovery tools, bulk-loading user records from HR, or receiving nightly feeds from monitoring platforms. Import Sets and Transform Maps are ServiceNow's native answer to this challenge.

This guide walks you through the full pipeline: from loading raw data into a staging table, through the Transform Map logic that routes it to target tables, to troubleshooting errors and building production-grade import processes.

The Import Pipeline

Source Data (CSV, Excel, SOAP, REST, JDBC)
    ↓
Import Set Table (Staging)
    ↓  
Transform Map (Field Mapping + Scripts)
    ↓
Target Table (incident, cmdb_ci, sys_user, etc.)

The staging table acts as a buffer. Your raw data lands there unchanged, and the Transform Map decides what to do with each field.

Import Sets: The Staging Layer

Creating an Import Set Data Source

Navigate to System Import Sets > Administration > Data Sources > New

Key configuration:

  • Type: File (for CSV/Excel), JDBC (for database), REST (for API), SOAP, LDAP
  • Format: CSV, Excel, XML, JSON
  • Load attachment: Import from a file attached to the data source record

Supported File Formats

Format Notes
CSV Most common; set delimiter explicitly
Excel (.xlsx) Column headers become field names
XML Define element/row XPath
JSON Define record path and field mapping
JDBC Direct database connection; define SQL query

Loading Data

Once your data source is configured:

Data Source record → Load All Records

This creates the Import Set table (prefixed u_import_) and populates it with raw rows. No target records are created yet.

Transform Maps: The Mapping Layer

Creating a Transform Map

System Import Sets > Administration > Transform Maps > New
  • Name: Descriptive (e.g., CSV to Incident – IT Migration)
  • Source Table: Your import set table
  • Target Table: The destination (e.g., incident)

Field Maps

Each field map connects one source column to one target field:

Source Field Target Field Notes
u_short_desc short_description Direct mapping
u_caller_email caller_id Coalesce lookup by email
u_priority_text priority Script to convert text to integer

Coalesce: The Upsert Key

Coalesce is the most important concept in Transform Maps. It defines which field(s) determine whether a record should be updated (if it already exists) or inserted (if it doesn't).

Field Map: u_ticket_number → number
Coalesce: ✅ Checked

With coalesce enabled on number, the transform engine will:

  1. Search the target table for a record where number = u_ticket_number
  2. If found → UPDATE that record
  3. If not found → INSERT a new record

You can coalesce on multiple fields (all must match for an update).

Reference Field Lookups

When your target field is a reference (like caller_id pointing to sys_user), you need a Coalesce on configuration:

Source Field: u_caller_email
Target Field: caller_id
Coalesce: ✅
Reference: sys_user
Reference field: email

The engine will find the sys_user record where email = u_caller_email and use that record's sys_id as the value for caller_id.

Transform Scripts

Field-Level Transform Script

Apply logic to a single field before it's mapped:

// Source has priority as text: "High", "Medium", "Low"
// Target expects integer: 1, 2, 3
var priorityMap = {
    'Critical': '1',
    'High': '2',
    'Medium': '3',
    'Low': '4'
};
answer = priorityMap[source.u_priority] || '4';

Transform Map-Level Scripts

Three event hooks run at different points:

onBefore: Runs before the record is inserted/updated. Cancel the import of invalid rows:

// Skip rows where the source email is blank
if (source.u_caller_email == '' || source.u_caller_email == null) {
    ignore = true; // Don't import this row
    log.warn('Skipping row ' + source.sys_import_row + 
             ': missing caller email');
}

onAfter: Runs after the record is inserted/updated. Add related records or trigger workflows:

// After importing an incident, create a default work note
var note = new GlideRecord('sys_journal_field');
note.initialize();
note.setValue('element_id', target.getUniqueValue());
note.setValue('element', 'work_notes');
note.setValue('value', 'Record migrated from legacy system on ' + 
    gs.now());
note.insert();

onComplete: Runs once after all rows are processed. Send a summary email:

var successCount = map_object.getSuccessCount();
var errorCount = map_object.getErrorCount();
gs.sendEmail(
    'migration-team@company.com',
    'Import Complete',
    'Processed: ' + (successCount + errorCount) + 
    '\nSuccess: ' + successCount + 
    '\nErrors: ' + errorCount
);

Transform States and Troubleshooting

Row States

State Meaning
Inserted New record created successfully
Updated Existing record updated
Ignored Skipped by ignore = true in script
Error Transform failed for this row

Common Errors and Fixes

Error Cause Fix
Mandatory field missing Target field required but source is blank Add default value in field map or onBefore script
Reference not found Coalesce lookup returned no match Check source data quality; add fallback in script
Value too long Source string exceeds target field max length Truncate in transform script: answer = source.u_desc.substring(0, 4000)
Duplicate coalesce Two rows have same coalesce key Deduplicate source data before import

Scheduled Imports

For ongoing data feeds, schedule your import:

Data Source → Scheduled Import Sets → New

Configure:

  • Frequency: Hourly, Daily, Weekly, CRON expression
  • Transform Map: Which map to run
  • Run as: Service account with appropriate permissions

Best Practices Checklist

  • Always test with a small sample file before full load
  • Use meaningful names for import sets and transform maps
  • Set coalesce keys that uniquely identify records in the target
  • Use onBefore scripts to skip invalid/incomplete rows gracefully
  • Log errors and warnings with log.error() and log.warn()
  • Clean up old import set tables periodically (they can consume significant space)
  • Use a service account for scheduled imports (not a named user)
  • Document the source-to-target field mapping in the transform map description

Conclusion

Import Sets and Transform Maps form a flexible, scriptable data pipeline that can handle everything from a one-time CSV migration to a nightly feed from an external ITSM tool. The coalesce mechanism is your key to safe upserts, and the three-stage scripting model (onBefore, onAfter, onComplete) gives you precise control over every aspect of the import lifecycle.

Keep reading this guide

Log in to access the full study guide and supercharge your preparation.