Data Import

Data Import

Overview

This skill covers the complete data import lifecycle in ServiceNow using Import Sets and Transform Maps:

• Understanding the Import Set architecture
• Configuring data sources (file, JDBC, LDAP, REST)
• Creating and configuring Transform Maps
• Field mapping strategies and transformations
• Transform scripts (onBefore, onAfter, onStart, onComplete)
• Coalesce fields for matching and deduplication
• Error handling and rollback strategies
• Scheduled imports and automation
• Performance optimization for large datasets

When to use: When importing external data into ServiceNow, performing ETL operations, migrating data between systems, or setting up recurring data synchronization.

Who should use this: Developers, administrators, integration specialists, and data migration teams.

Prerequisites

• Roles: import_admin, import_transformer, or admin
• Access: Target tables, import set tables, and data source configuration
• Knowledge: ServiceNow data model, GlideRecord API, table relationships
• Related Skills:
• admin/generic-crud-operations - Basic CRUD operations
• admin/batch-operations - Bulk data handling

Import Set Architecture

Key Components

┌─────────────────┐    ┌──────────────────┐    ┌─────────────────┐
│   Data Source   │───>│   Import Set     │───>│  Transform Map  │
│  (File/JDBC/    │    │    (Staging)     │    │   (Mapping)     │
│   LDAP/REST)    │    │                  │    │                 │
└─────────────────┘    └──────────────────┘    └─────────────────┘
                                                       │
                                                       v
                                               ┌─────────────────┐
                                               │  Target Table   │
                                               │   (Production)  │
                                               └─────────────────┘

Core Tables

| Table | Purpose | |-------|---------| | sys_import_set | Import set header records | | sys_import_set_row | Staging table for imported data | | sys_transform_map | Transform map definitions | | sys_transform_entry | Field mapping entries | | sys_transform_script | Transform scripts (onBefore, etc.) | | sys_data_source | Data source configurations |

Import States

| State | Value | Description | |-------|-------|-------------| | Loaded | loaded | Data loaded into staging | | Transformed | transformed | Successfully transformed | | Error | error | Transform failed | | Ignored | ignored | Skipped by transform logic |

Procedure

Phase 1: Data Source Configuration

Step 1.1: Create File Data Source

For CSV, Excel, or XML file imports.

Using MCP:

Tool: SN-Create-Record
Parameters:
  table_name: sys_data_source
  data:
    name: Employee Import - CSV
    type: File
    format: CSV
    header_row: 1
    sheet_number: 1
    import_set_table_name: u_employee_import
    active: true

Response:

{
  "sys_id": "abc123...",
  "name": "Employee Import - CSV",
  "type": "File",
  "import_set_table_name": "u_employee_import"
}

Step 1.2: Create JDBC Data Source

For database connections (Oracle, MySQL, SQL Server).

Tool: SN-Create-Record
Parameters:
  table_name: sys_data_source
  data:
    name: HR Database - JDBC
    type: JDBC
    connection_url: jdbc:mysql://hr-db.company.com:3306/hrms
    user: servicenow_reader
    password: [encrypted_password]
    import_set_table_name: u_hr_import
    query: |
      SELECT employee_id, first_name, last_name, email, department, hire_date
      FROM employees
      WHERE modified_date > ?
    active: true

JDBC Connection URL Patterns:

| Database | Connection URL | |----------|----------------| | MySQL | jdbc:mysql://host:3306/database | | Oracle | jdbc:oracle:thin:@host:1521:sid | | SQL Server | jdbc:sqlserver://host:1433;databaseName=db | | PostgreSQL | jdbc:postgresql://host:5432/database |

Step 1.3: Create LDAP Data Source

For Active Directory or LDAP directory imports.

Tool: SN-Create-Record
Parameters:
  table_name: sys_data_source
  data:
    name: Active Directory Users
    type: LDAP
    server_url: ldap://ad.company.com:389
    user: CN=ServiceNow,OU=Service Accounts,DC=company,DC=com
    password: [encrypted_password]
    import_set_table_name: u_ldap_user_import
    ldap_target: OU=Users,DC=company,DC=com
    ldap_filter: (&(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))
    active: true

Step 1.4: Create REST Data Source

For REST API integrations.

Tool: SN-Create-Record
Parameters:
  table_name: sys_data_source
  data:
    name: External API - REST
    type: REST (IntegrationHub)
    connection_url: https://api.external-system.com/v1/records
    http_method: GET
    authentication_type: basic
    user: api_user
    password: [encrypted_password]
    import_set_table_name: u_api_import
    format: JSON
    active: true

Phase 2: Import Set Table Creation

Step 2.1: Create Custom Import Set Table

Create a staging table to receive imported data.

Using MCP:

Tool: SN-Create-Record
Parameters:
  table_name: sys_db_object
  data:
    name: u_employee_import
    label: Employee Import
    extends: sys_import_set_row
    create_access: true
    read_access: true
    update_access: true
    delete_access: true

Step 2.2: Add Columns to Import Set Table

Define columns matching your source data structure.

Batch Create Columns:

Tool: SN-Batch-Create
Parameters:
  records:
    - table_name: sys_dictionary
      data:
        name: u_employee_import
        element: u_employee_id
        column_label: Employee ID
        internal_type: string
        max_length: 40
    - table_name: sys_dictionary
      data:
        name: u_employee_import
        element: u_first_name
        column_label: First Name
        internal_type: string
        max_length: 100
    - table_name: sys_dictionary
      data:
        name: u_employee_import
        element: u_last_name
        column_label: Last Name
        internal_type: string
        max_length: 100
    - table_name: sys_dictionary
      data:
        name: u_employee_import
        element: u_email
        column_label: Email
        internal_type: string
        max_length: 255
    - table_name: sys_dictionary
      data:
        name: u_employee_import
        element: u_department
        column_label: Department
        internal_type: string
        max_length: 100
    - table_name: sys_dictionary
      data:
        name: u_employee_import
        element: u_hire_date
        column_label: Hire Date
        internal_type: string
        max_length: 40

Phase 3: Transform Map Configuration

Step 3.1: Create Transform Map

Define how staging data transforms to target table.

Using MCP:

Tool: SN-Create-Record
Parameters:
  table_name: sys_transform_map
  data:
    name: Employee Import Transform
    source_table: u_employee_import
    target_table: sys_user
    active: true
    enforce_mandatory_fields: true
    run_business_rules: true
    run_script: true
    order: 100

Transform Map Options:

| Option | Description | |--------|-------------| | enforce_mandatory_fields | Fail if mandatory fields missing | | run_business_rules | Execute business rules on target | | run_script | Run transform scripts | | copy_empty_fields | Overwrite with empty values | | order | Execution order (lower = earlier) |

Step 3.2: Create Field Mappings

Map source columns to target fields.

Batch Create Field Mappings:

Tool: SN-Batch-Create
Parameters:
  records:
    - table_name: sys_transform_entry
      data:
        map: [transform_map_sys_id]
        source_field: u_employee_id
        target_field: employee_number
        coalesce: true
        order: 100
    - table_name: sys_transform_entry
      data:
        map: [transform_map_sys_id]
        source_field: u_first_name
        target_field: first_name
        order: 200
    - table_name: sys_transform_entry
      data:
        map: [transform_map_sys_id]
        source_field: u_last_name
        target_field: last_name
        order: 300
    - table_name: sys_transform_entry
      data:
        map: [transform_map_sys_id]
        source_field: u_email
        target_field: email
        coalesce: true
        order: 400
    - table_name: sys_transform_entry
      data:
        map: [transform_map_sys_id]
        source_field: u_department
        target_field: department
        reference_qual_mapping: true
        order: 500

Step 3.3: Field Mapping Types

| Type | Use Case | Configuration | |------|----------|---------------| | Direct | Simple copy | Source to target, no transformation | | Mapping | Value translation | Use choice map or script | | Reference | Lookup relation | Set reference_qual_mapping: true | | Script | Complex logic | Use source_script field | | Derived | Calculated | No source, only script |

Script Mapping Example:

Tool: SN-Create-Record
Parameters:
  table_name: sys_transform_entry
  data:
    map: [transform_map_sys_id]
    source_field: u_status
    target_field: active
    use_source_script: true
    source_script: |
      // Convert status to boolean active flag
      answer = (source.u_status == 'Active' || source.u_status == 'A') ? 'true' : 'false';
    order: 600

Reference Mapping with Lookup:

Tool: SN-Create-Record
Parameters:
  table_name: sys_transform_entry
  data:
    map: [transform_map_sys_id]
    source_field: u_manager_email
    target_field: manager
    reference_qual_mapping: true
    reference_qual: email=[u_manager_email]
    order: 700

Phase 4: Coalesce Fields (Matching)

Step 4.1: Understanding Coalesce

Coalesce fields determine if the transform should INSERT or UPDATE:

• No Match: INSERT new record
• Single Match: UPDATE existing record
• Multiple Matches: Error (unless configured otherwise)

Step 4.2: Configure Coalesce Fields

Single Coalesce Field:

Tool: SN-Update-Record
Parameters:
  table_name: sys_transform_entry
  sys_id: [entry_sys_id]
  data:
    coalesce: true

Multiple Coalesce Fields (Compound Key):

Tool: SN-Batch-Update
Parameters:
  updates:
    - table_name: sys_transform_entry
      sys_id: [employee_id_entry_sys_id]
      data:
        coalesce: true
    - table_name: sys_transform_entry
      sys_id: [company_entry_sys_id]
      data:
        coalesce: true

Coalesce Behavior Matrix:

| Scenario | Behavior | |----------|----------| | No coalesce fields | Always INSERT new record | | Coalesce, no match | INSERT new record | | Coalesce, one match | UPDATE existing record | | Coalesce, multiple matches | ERROR (configurable) |

Step 4.3: Handle Multiple Matches

Configure transform map to handle multiple matches.

Tool: SN-Update-Record
Parameters:
  table_name: sys_transform_map
  sys_id: [transform_map_sys_id]
  data:
    multi_coalesce_action: ignore

Multi-Coalesce Actions:

| Action | Behavior | |--------|----------| | create | Create new record anyway | | ignore | Skip row, mark as ignored | | update_first | Update first match | | reject | Mark row as error |

Phase 5: Transform Scripts

Step 5.1: Script Types Overview

| Script Type | Execution Point | Use Case | |-------------|-----------------|----------| | onStart | Before transform begins | Initialize counters, validation | | onBefore | Before each row | Row-level preprocessing | | onAfter | After each row | Post-processing, related records | | onComplete | After transform ends | Summary, notifications | | onChoiceCreate | When creating choice | Custom choice creation | | onForeignInsert | On reference insert | Handle missing references |

Step 5.2: Create onStart Script

Runs once at the beginning of the transform.

Tool: SN-Create-Record
Parameters:
  table_name: sys_transform_script
  data:
    map: [transform_map_sys_id]
    script_type: onStart
    script: |
      // onStart: Initialize transform
      // Available: log, source (first row), map, import_set

      log.info('Starting employee import transform');
      log.info('Import Set: ' + import_set.number);
      log.info('Source table: ' + map.source_table);

      // Initialize counters in scratchpad
      var scratchpad = {};
      scratchpad.processed = 0;
      scratchpad.created = 0;
      scratchpad.updated = 0;
      scratchpad.errors = 0;
      scratchpad.startTime = new GlideDateTime();
    order: 100
    active: true

Step 5.3: Create onBefore Script

Runs before each row is transformed.

Tool: SN-Create-Record
Parameters:
  table_name: sys_transform_script
  data:
    map: [transform_map_sys_id]
    script_type: onBefore
    script: |
      // onBefore: Row-level preprocessing
      // Available: source, target, map, log, action, error, ignore
      // Set ignore=true to skip row, error=true to mark as error

      // Validate required fields
      if (!source.u_employee_id || source.u_employee_id.nil()) {
        error = true;
        error_message = 'Missing employee ID';
        return;
      }

      if (!source.u_email || source.u_email.nil()) {
        error = true;
        error_message = 'Missing email address';
        return;
      }

      // Validate email format
      var emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
      if (!emailRegex.test(source.u_email.toString())) {
        error = true;
        error_message = 'Invalid email format: ' + source.u_email;
        return;
      }

      // Normalize data
      source.u_first_name = source.u_first_name.toString().trim();
      source.u_last_name = source.u_last_name.toString().trim();
      source.u_email = source.u_email.toString().toLowerCase().trim();

      // Generate username if not provided
      if (!source.u_user_name || source.u_user_name.nil()) {
        source.u_user_name = source.u_email.toString().split('@')[0];
      }

      // Conditional skip
      if (source.u_status == 'Terminated') {
        ignore = true;
        return;
      }

      scratchpad.processed++;
    order: 100
    active: true

Step 5.4: Create onAfter Script

Runs after each row is transformed.

Tool: SN-Create-Record
Parameters:
  table_name: sys_transform_script
  data:
    map: [transform_map_sys_id]
    script_type: onAfter
    script: |
      // onAfter: Post-processing
      // Available: source, target, map, log, action, error, scratchpad
      // action = 'insert', 'update', or 'ignore'

      if (action == 'insert') {
        scratchpad.created++;

        // Add user to default groups
        if (target.sys_id) {
          addUserToGroups(target.sys_id, source.u_department);
        }

        log.info('Created user: ' + target.user_name);

      } else if (action == 'update') {
        scratchpad.updated++;
        log.info('Updated user: ' + target.user_name);
      }

      // Create related records
      if (source.u_manager_email && !source.u_manager_email.nil()) {
        // Store for later processing
        scratchpad.managersToProcess = scratchpad.managersToProcess || [];
        scratchpad.managersToProcess.push({
          userId: target.sys_id.toString(),
          managerEmail: source.u_manager_email.toString()
        });
      }

      function addUserToGroups(userId, department) {
        var deptGroups = {
          'IT': ['IT Support', 'Service Desk'],
          'HR': ['HR Team'],
          'Finance': ['Finance Team']
        };

        var groups = deptGroups[department] || [];

        groups.forEach(function(groupName) {
          var group = new GlideRecord('sys_user_group');
          group.addQuery('name', groupName);
          group.query();

          if (group.next()) {
            var member = new GlideRecord('sys_user_grmember');

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [Happy-Technologies-LLC](https://github.com/Happy-Technologies-LLC)
- **Source:** [Happy-Technologies-LLC/happy-platform-skills](https://github.com/Happy-Technologies-LLC/happy-platform-skills)
- **License:** Apache-2.0

Install and usage instructions live in the source repository linked above.