Transform Maps for ServiceNow

Transform Maps control how data from import sets is mapped and transformed into ServiceNow tables.

Import Architecture

Data Source (CSV, LDAP, JDBC, REST) ↓ Import Set Table (staging) ↓ Transform Map (mapping rules) ↓ Target Table (final destination)

Key Components

Component Table Purpose

Data Source sys_data_source Connection configuration

Import Set Table sys_db_object Staging table

Import Set sys_import_set Import run record

Transform Map sys_transform_map Mapping definition

Field Map sys_transform_entry Field mappings

Data Sources

CSV Data Source (ES5)

// Create CSV data source var ds = new GlideRecord("sys_data_source") ds.initialize() ds.setValue("name", "Employee Import - CSV") ds.setValue("type", "File") ds.setValue("format", "CSV")

// File settings ds.setValue("file_path", "/import/employees.csv") ds.setValue("header_row", 1)

// CSV parsing ds.setValue("csv_delimiter", ",") ds.setValue("csv_quote", '"')

// Import set table ds.setValue("import_set_table", "u_employee_import")

ds.insert()

JDBC Data Source (ES5)

// Create JDBC data source var ds = new GlideRecord("sys_data_source") ds.initialize() ds.setValue("name", "HR System - JDBC") ds.setValue("type", "JDBC")

// Connection ds.setValue("connection_url", "jdbc:oracle:thin:@hrdb:1521:HRPROD") ds.setValue("username", "hr_readonly") ds.setValue("password", "encrypted_password")

// Query ds.setValue("query", "SELECT emp_id, first_name, last_name, email, dept_code FROM employees WHERE active = 1")

// Import set table ds.setValue("import_set_table", "u_hr_employee_import")

ds.insert()

REST Data Source (ES5)

// Create REST data source var ds = new GlideRecord("sys_data_source") ds.initialize() ds.setValue("name", "External API - REST") ds.setValue("type", "REST (IntegrationHub)")

// REST message ds.setValue("rest_message", restMessageSysId) ds.setValue("http_method", "GET")

// Response handling ds.setValue("json_path", "$.data.employees[*]")

// Import set table ds.setValue("import_set_table", "u_api_employee_import")

ds.insert()

Import Set Tables

Creating Import Set Table (ES5)

// Create staging table for employee import var table = new GlideRecord("sys_db_object") table.initialize() table.setValue("name", "u_employee_import") table.setValue("label", "Employee Import") table.setValue("super_class", "sys_import_set_row") // Extends import set row table.setValue("is_extendable", false) table.insert()

// Add columns matching source data var columns = [ { name: "u_employee_id", type: "string", max_length: 50 }, { name: "u_first_name", type: "string", max_length: 100 }, { name: "u_last_name", type: "string", max_length: 100 }, { name: "u_email", type: "string", max_length: 255 }, { name: "u_department", type: "string", max_length: 100 }, { name: "u_manager_id", type: "string", max_length: 50 }, { name: "u_start_date", type: "string", max_length: 20 }, ]

for (var i = 0; i < columns.length; i++) { var col = new GlideRecord("sys_dictionary") col.initialize() col.setValue("name", "u_employee_import") col.setValue("element", columns[i].name) col.setValue("internal_type", columns[i].type) col.setValue("max_length", columns[i].max_length) col.insert() }

Transform Maps

Creating Transform Map (ES5)

// Create transform map var tm = new GlideRecord("sys_transform_map") tm.initialize() tm.setValue("name", "Employee Import Transform") tm.setValue("source_table", "u_employee_import") tm.setValue("target_table", "sys_user")

// Run order (for multiple transforms) tm.setValue("order", 100)

// Active tm.setValue("active", true)

// Copy empty fields tm.setValue("copy_empty_fields", false)

// Enforce mandatory fields tm.setValue("enforce_mandatory_fields", true)

var tmSysId = tm.insert()

Field Mappings

Direct Field Mapping (ES5)

// Map source fields to target fields function addFieldMap(transformMapId, sourceField, targetField, config) { var fm = new GlideRecord("sys_transform_entry") fm.initialize() fm.setValue("map", transformMapId) fm.setValue("source_field", sourceField) fm.setValue("target_field", targetField)

// Coalesce (match existing records) if (config && config.coalesce) { fm.setValue("coalesce", true) }

// Order fm.setValue("order", config ? config.order : 100)

return fm.insert() }

// Map employee fields addFieldMap(tmSysId, "u_employee_id", "employee_number", { coalesce: true, order: 10 }) addFieldMap(tmSysId, "u_first_name", "first_name", { order: 20 }) addFieldMap(tmSysId, "u_last_name", "last_name", { order: 30 }) addFieldMap(tmSysId, "u_email", "email", { order: 40 })

Reference Field Mapping (ES5)

// Map to reference field (lookup by value) var deptMap = new GlideRecord("sys_transform_entry") deptMap.initialize() deptMap.setValue("map", tmSysId) deptMap.setValue("source_field", "u_department") deptMap.setValue("target_field", "department")

// Reference handling deptMap.setValue("reference_key", true) deptMap.setValue("reference_key_field", "name") // Lookup by department name

// Create if not found deptMap.setValue("create_also", false)

deptMap.insert()

Scripted Field Mapping (ES5)

// Script-based field transformation var scriptMap = new GlideRecord("sys_transform_entry") scriptMap.initialize() scriptMap.setValue("map", tmSysId) scriptMap.setValue("target_field", "name") scriptMap.setValue( "source_script", "// Combine first and last name\n" + 'answer = source.u_first_name + " " + source.u_last_name;', ) scriptMap.insert()

// Date transformation script var dateMap = new GlideRecord("sys_transform_entry") dateMap.initialize() dateMap.setValue("map", tmSysId) dateMap.setValue("target_field", "u_start_date") dateMap.setValue( "source_script", "// Convert MM/DD/YYYY to ServiceNow date format\n" + 'var parts = source.u_start_date.split("/");\n' + "if (parts.length === 3) {\n" + ' answer = parts[2] + "-" + parts[0] + "-" + parts[1];\n' + "} else {\n" + ' answer = "";\n' + "}", ) dateMap.insert()

Coalesce (Update vs Insert)

Coalesce Configuration

// Coalesce on employee_number to update existing records var coalesceMap = new GlideRecord("sys_transform_entry") coalesceMap.initialize() coalesceMap.setValue("map", tmSysId) coalesceMap.setValue("source_field", "u_employee_id") coalesceMap.setValue("target_field", "employee_number") coalesceMap.setValue("coalesce", true) // KEY: Use for matching coalesceMap.setValue("order", 1) // Process first coalesceMap.insert()

// Multiple coalesce fields (compound key) // First field with coalesce=true, second with coalesce=true // Both must match for update

Transform Scripts

onBefore Script (ES5)

// Transform Map > onBefore script // Runs before each row is processed

;(function runTransformScript(source, map, log, target) { // Skip inactive employees if (source.u_status === "INACTIVE") { ignore = true // Skip this row return }

// Validate required fields if (!source.u_employee_id || !source.u_email) { log.error("Missing required fields for row: " + source.sys_id) ignore = true return }

// Normalize email source.u_email = source.u_email.toString().toLowerCase() })(source, map, log, target)

onAfter Script (ES5)

// Transform Map > onAfter script // Runs after each row is processed

;(function runTransformScript(source, map, log, target) { // Add user to appropriate group based on department if (target && action !== "ignore") { var dept = target.department.getDisplayValue() var groupName = ""

if (dept === "IT") {
  groupName = "IT Staff"
} else if (dept === "HR") {
  groupName = "HR Team"
}

if (groupName) {
  addUserToGroup(target.sys_id, groupName)
}

}

function addUserToGroup(userId, groupName) { var group = new GlideRecord("sys_user_group") group.addQuery("name", groupName) group.query()

if (group.next()) {
  var member = new GlideRecord("sys_user_grmember")
  member.addQuery("user", userId)
  member.addQuery("group", group.getUniqueValue())
  member.query()

  if (!member.next()) {
    member.initialize()
    member.setValue("user", userId)
    member.setValue("group", group.getUniqueValue())
    member.insert()
  }
}

} })(source, map, log, target)

onComplete Script (ES5)

// Transform Map > onComplete script // Runs after all rows are processed

;(function runTransformScript(source, map, log, target) { // Log import statistics var importSet = new GlideRecord("sys_import_set") if (importSet.get(source.sys_import_set)) { var stats = { total: importSet.getValue("rows"), inserted: importSet.getValue("insertions"), updated: importSet.getValue("updates"), errors: importSet.getValue("errors"), }

log.info("Import completed: " + JSON.stringify(stats))

// Send notification if errors
if (stats.errors > 0) {
  gs.eventQueue("import.errors", importSet, stats.errors.toString())
}

} })(source, map, log, target)

Running Imports

Manual Import Execution (ES5)

// Execute import programmatically var loader = new GlideImportSetLoader(dataSourceSysId) var importSetSysId = loader.loadImportSet()

if (importSetSysId) { // Run transform var transformer = new GlideImportSetTransformer() transformer.setImportSetID(importSetSysId) transformer.transform()

// Check results var importSet = new GlideRecord("sys_import_set") if (importSet.get(importSetSysId)) { gs.info("Import completed: " + importSet.getValue("state")) gs.info("Rows: " + importSet.getValue("rows")) gs.info("Errors: " + importSet.getValue("errors")) } }

MCP Tool Integration

Available Import Tools

Tool Purpose

snow_create_transform_map

Create transform map

snow_create_field_map

Add field mapping

snow_create_import_set

Create import set

snow_discover_data_sources

Find data sources

snow_test_integration

Test connection

Example Workflow

// 1. Create import set table await snow_create_import_set_table({ name: "u_vendor_import", fields: [ { name: "u_vendor_id", type: "string" }, { name: "u_vendor_name", type: "string" }, { name: "u_contact_email", type: "string" }, ], })

// 2. Create transform map var transformId = await snow_create_transform_map({ name: "Vendor Import", source_table: "u_vendor_import", target_table: "core_company", })

// 3. Add field mappings await snow_create_field_map({ transform_map: transformId, source: "u_vendor_id", target: "vendor_code", coalesce: true, })

await snow_create_field_map({ transform_map: transformId, source: "u_vendor_name", target: "name", })

// 4. Run import await snow_execute_import({ data_source: dataSourceId, transform_map: transformId, })

Best Practices

• Staging Tables - Always use import set tables

• Coalesce Keys - Define clear matching criteria

• Validate Data - Use onBefore scripts

• Error Handling - Log and handle failures

• Incremental Imports - Track last import date

• Testing - Test with small datasets first

• Rollback Plan - Be able to undo imports

• Scheduling - Use scheduled data sources