Import Sets

Import Sets in ServiceNow: A Comprehensive Guide for CAD Exam Preparation

Introduction to Import Sets

Import Sets are one of the most critical features in ServiceNow for working with external data. Whether you're migrating data from a legacy system, integrating with third-party applications, or performing routine data loads, Import Sets provide a robust, auditable, and flexible mechanism to bring external data into the ServiceNow platform. For the Certified Application Developer (CAD) exam, understanding Import Sets thoroughly is essential, as they represent a core concept in the "Working with External Data" domain.

Why Are Import Sets Important?

In real-world enterprise environments, ServiceNow rarely operates in isolation. Organizations need to:

- Migrate data from legacy systems during initial platform deployment
- Synchronize data regularly from external sources such as HR systems, asset management tools, or CRM platforms
- Bulk load records for operational needs such as onboarding users or updating configuration items
- Maintain data integrity by applying transformation rules and validation logic before data reaches production tables

Import Sets address all of these needs by providing a staging area where data lands first before being cleaned, transformed, and mapped to target tables. This ensures that raw, potentially messy external data does not directly corrupt production data.

What Are Import Sets?

An Import Set is a temporary staging table in ServiceNow that stores data imported from an external source. The data in an Import Set table does not immediately go into a production table. Instead, it sits in the staging table until a Transform Map processes and moves it to the appropriate target table.

The key components of the Import Set framework include:

1. Data Source
A Data Source defines where the external data comes from. Supported data source types include:
- File (CSV, Excel, XML, JSON)
- JDBC (direct database connections)
- LDAP (directory services)
- Custom (via scripted data loading)

2. Import Set Table (Staging Table)
When data is loaded, ServiceNow automatically creates a staging table (prefixed with u_imp_ or similar naming conventions). This table temporarily holds the raw imported data. Import Set tables are designed to be transient — they hold data only long enough for transformation to occur.

3. Transform Map
A Transform Map defines the relationship between the Import Set table (source) and the target table (destination). It contains:
- Field Maps: Mappings between source columns and target fields
- Coalesce Fields: Fields used to determine whether to insert a new record or update an existing one
- Transform Scripts: Scripts that run during transformation for complex logic (onBefore, onAfter, onStart, onComplete, onForeignInsert, onChoiceCreate, onReject)

4. Import Set Run
Each time an import is executed, an Import Set Run record is created. This provides an audit trail showing how many records were inserted, updated, ignored, or errored during the process.

How Import Sets Work: The Complete Process Flow

Understanding the end-to-end flow is crucial for the CAD exam:

Step 1: Define or Select a Data Source
Navigate to System Import Sets > Data Sources and create or select a data source. You specify the type (File, JDBC, etc.), the file or connection details, and any sheet or table specifications.

Step 2: Load Data into the Staging Table
When you load data (either manually via the UI, via a scheduled import, or through the API), ServiceNow reads the external data and populates an Import Set staging table. If the staging table doesn't already exist, ServiceNow auto-creates it with columns matching the source data.

Step 3: Create a Transform Map
Navigate to System Import Sets > Transform Maps. Create a new Transform Map that links the Import Set table to the target table. You can use Auto Map Matching Fields to automatically map source columns to target fields that share the same name. For fields that don't auto-match, you manually create Field Maps.

Step 4: Configure Coalesce Fields
Coalesce fields are critical. They tell ServiceNow how to determine if an incoming record should update an existing record or insert a new one. If a record in the target table matches on all coalesced fields, the existing record is updated. If no match is found, a new record is created. If no coalesce field is set, every import will insert new records, potentially creating duplicates.

Step 5: Add Transform Scripts (Optional)
For complex transformation logic, you can add scripts at various events during the transformation process:
- onBefore: Runs before each record is transformed (use source and target objects to manipulate data)
- onAfter: Runs after each record is transformed and saved
- onStart: Runs once at the beginning of the entire transformation
- onComplete: Runs once after all records are transformed
- onForeignInsert: Runs when a reference field value doesn't exist in the referenced table
- onChoiceCreate: Runs when a choice value doesn't exist
- onReject: Runs when a record is rejected during transformation

Step 6: Run the Transform
Execute the transformation either manually or as part of a scheduled process. ServiceNow processes each row in the staging table according to the Transform Map rules.

Step 7: Review Results
Check the Import Set Run and Import Log for results. Each record will have a status: Inserted, Updated, Ignored, or Error. The import log provides detailed information about any issues encountered.

Key Concepts to Understand for the Exam

Coalesce Fields
This is one of the most commonly tested topics. Remember:
- Coalesce determines insert vs. update behavior
- Multiple coalesce fields use AND logic (all must match for an update)
- If no coalesce field is defined, all records are inserted as new
- Coalesce is set on individual Field Maps, not on the Transform Map itself

Transform Map Scripts vs. Field Maps
- Field Maps handle straightforward column-to-field mappings
- Transform scripts handle complex logic that cannot be expressed through simple mapping
- The source object represents the Import Set row; the target object represents the destination record
- Setting ignore = true in an onBefore script will skip that record

Scheduled Imports
Data Sources can be configured with a scheduled import to run automatically. This is commonly used for recurring integrations such as nightly HR data syncs.

Import Set Table Cleanup
Import Set tables are staging tables and should be cleaned up regularly. ServiceNow provides a scheduled job called Clean Import Set Tables that removes old import set data. This is important for performance and storage management.

Robust Import Set Transformers (RIT)
ServiceNow uses Robust Import Set Transformers by default. Key characteristics include:
- Each row is processed in its own database transaction
- If one row fails, it does not affect other rows
- This provides better error handling and reliability compared to the legacy transformer

Import Set API
The Import Set API (Table API endpoint: /api/now/import/{staging_table_name}) allows external systems to push data into ServiceNow via REST. When data is posted to this API, it is loaded into the staging table and can optionally trigger the associated Transform Map automatically. This is a modern and commonly used integration pattern.

Choice Action and Reference Handling
Transform Maps provide options for handling references and choices:
- Choice Action: What to do when an imported choice value doesn't exist (create, reject, or ignore)
- Reference Handling: What to do when a referenced record doesn't exist (create, reject, or ignore)

Common Exam Scenarios

Scenario 1: Duplicate Records After Import
If a question describes duplicate records appearing after an import, the likely cause is that no coalesce field was set on the Transform Map, causing all records to be inserted rather than matched and updated.

Scenario 2: Skipping Certain Records During Import
If a question asks how to skip records based on a condition, the answer is to use an onBefore transform script and set ignore = true for records that meet the skip criteria.

Scenario 3: Running Logic After All Records Are Processed
Use an onComplete transform script to execute logic after the entire import set has been processed (e.g., sending a notification or updating a status).

Scenario 4: External System Pushes Data to ServiceNow
The recommended approach is to use the Import Set API, which accepts data via REST, loads it into a staging table, and triggers the Transform Map.

Exam Tips: Answering Questions on Import Sets

1. Always think about the full pipeline: Data Source → Import Set Table → Transform Map → Target Table. Many questions test your understanding of this flow and which component is responsible for what.

2. Coalesce is king: Whenever a question mentions duplicate records, matching, or update-vs-insert behavior, immediately think about coalesce fields. Remember that coalesce uses AND logic when multiple fields are set.

3. Know your transform script events: Be clear on when each script event fires. onBefore and onAfter run per record; onStart and onComplete run once per transformation. Know that source and target objects are available in onBefore and onAfter scripts.

4. Distinguish between staging and target tables: Import Set tables are staging tables. Data does NOT go directly to the target table — it must be transformed first. If a question asks about where imported data initially resides, the answer is the Import Set (staging) table.

5. Remember the Import Set API: For REST-based integrations where an external system pushes data to ServiceNow, the Import Set API is the recommended approach. It combines data loading and transformation in one step.

6. Cleanup matters: If a question references performance issues related to imports or large amounts of staging data, think about the Clean Import Set Tables scheduled job.

7. Read questions carefully for keywords: Words like "before transformation," "after each record," "after all records," "staging," and "mapping" are clues pointing to specific components or scripts in the Import Set framework.

8. Robust Import Set Transformer behavior: Remember that with RIT, each record is processed independently. A failure in one record does not roll back or affect others. This is a testable detail.

9. Auto-mapping: Know that the "Auto Map Matching Fields" feature maps source columns to target fields with the same name. Fields that don't match must be manually mapped or handled via script.

10. Elimination strategy: If you're unsure, eliminate answers that suggest data goes directly to a production table without transformation, or answers that confuse Data Sources with Transform Maps. These are common distractors.

Summary

Import Sets are ServiceNow's primary mechanism for bringing external data into the platform in a controlled, auditable, and flexible manner. The framework consists of Data Sources, Import Set (staging) tables, Transform Maps with Field Maps and coalesce logic, and Transform Scripts for complex processing. Mastering the end-to-end flow, understanding coalesce behavior, and knowing the various transform script events will prepare you well for any Import Set question on the CAD exam.