Transform Maps for ServiceNow Skill

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

Agent Skills: Transform Maps for ServiceNow

Install this agent skill to your local

Skill Files