# Data Import

> Master ServiceNow data import using Import Sets, Transform Maps, and various data sources with robust error handling and performance optimization

- **Type:** Skill
- **Install:** `agentstack add skill-happy-technologies-llc-happy-platform-skills-data-import`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [Happy-Technologies-LLC](https://agentstack.voostack.com/s/happy-technologies-llc)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** Apache-2.0
- **Upstream author:** [Happy-Technologies-LLC](https://github.com/Happy-Technologies-LLC)
- **Source:** https://github.com/Happy-Technologies-LLC/happy-platform-skills/tree/main/skills/development/data-import

## Install

```sh
agentstack add skill-happy-technologies-llc-happy-platform-skills-data-import
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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:**
```json
{
  "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.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-happy-technologies-llc-happy-platform-skills-data-import
- Seller: https://agentstack.voostack.com/s/happy-technologies-llc
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.