Import sets key concepts

  • Release version: Xanadu
  • Updated August 1, 2024
  • 5 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Import sets key concepts

    Import sets in ServiceNow serve as staging tables for data imported from various sources before transforming and integrating them into existing tables like Incident or Users. They enable controlled, incremental data import to avoid performance issues and system outages. Transform maps define how fields from import sets map to target tables, ensuring accurate and consistent data transformation. Import sets operate with the System user role and cannot modify encrypted fields.

    Show full answer Show less

    Key Concepts and Terminology

    • Import Set Table: Automatically generated tables that temporarily hold imported data. Manual column additions are discouraged to maintain cleanup integrity.
    • Data Source: Defines where data originates for import, such as files, JDBC databases, or LDAP directories.
    • Transformation: The process of converting import set records into target tables based on transform maps.
    • Transform Map: A set of field mappings from import set fields to target table fields. One import set field can map to multiple target fields.
    • Robust Import Set Transformer: An alternative to transform maps that separates transformation and processing, allowing flexible data loads to multiple target tables.
    • Foreign Record Insert: Occurs when an import updates a related, non-target table via reference fields (e.g., updating sysuser when importing incident caller data).
    • Coalesce: A setting on field maps that enables updating existing records during transformation if matching values exist, or creating new records if not.

    Supported Import Formats and Data Sources

    • File Formats: CSV (RFC4180 compliant), Excel (XLS/XLSX), XML (consistent XPath), JSON (RFC-4627 compliant with array root repeated), and Custom scripts for parsing.
    • External Data Sources: JDBC databases, LDAP, OIDC, ServiceNow REST via Integration Hub, and custom scripted loads—all requiring valid transform maps.

    Transform Maps versus Robust Import Set Transformers

    Both convert data from source to target, but transform maps operate from staging tables to destination tables, while robust import set transformers process data from source tables to message listeners. When both exist for a data source, the robust import set transformer takes precedence.

    Import Set Cleanup and Management

    • Scheduled Cleanup: The Import Set Deleter job runs daily at midnight to remove import sets and related records older than 7 days by default, preventing table bloat.
    • Activation Considerations: Before enabling cleanup, ensure import set tables do not exceed 5 million records collectively. If they do, coordinate manual deletion with Customer Service and Support.
    • Manual Deletion: Import set tables (e.g., Computer, User, Notification, Location) and related transform maps can be deleted manually, with options to remove data only or the entire table structure.

    Learn about import sets before importing data to your instance.

    The import sets table acts as a staging area for records imported from a data source.

    Note:
    Do not import data in extremely large chunks. Creating an extremely large import set can cause delays and system outages.

    A transform map determines the relationships between fields displaying in an import set table and fields in an existing ServiceNow table, such as the Incidents or Users table.

    Importing sets skip records when the data in the instance matches the data being imported.

    Note:
    Import sets run as user System and therefore cannot add data to encrypted fields.

    Import sets terminology

    Table 1. Key terms
    Term Definition
    Import Set table

    A table that acts as a staging location for records imported from a data source before transforming those records. Fields on these tables are generated automatically based on imported data and should not be modified manually.

    Note:
    Columns in an import set table are generated automatically and shouldn't be added manually. Manually adding columns can negatively impact the table's scheduled cleanup and may lead to an incomplete cleanup and orphaned records.

    If you need to add columns to an import set table, first add the column to the input data, then select Test Load 20 Records or Load All Records to auto generate the columns in the import set table.

    Columns in an import set table might not have the same type as the input data. For example, a currency column may be represented as a string column in an import set table until it's assigned to a target table column that is of type currency.
    Data source A record that defines where to get the data to import. A data source may point to a file, a JDBC-compatible database, or an LDAP organizational unit.
    Transformation The conversion of data from an import set table to another table according to the rules defined in a transform map.
    Transform map A set of field maps that define the relationships between fields in an import set and fields on a table, such as Incident.

    During transformation, data is copied from the Import Set table to the destination table based on the transform map.

    A single import set field may be mapped to multiple fields on other tables.
    Robust import set transformer Extract, transform, and load data to one or more target tables. Separate the transform and processing functions, providing a more flexible alternative to transform maps.
    Foreign record insert A foreign record insert occurs when an import makes a change to a table that is not the target table for that import.

    This change happens when updating a reference field on a table. For example, when updating a value for the caller on an incident, the import is actually updating the sys_user table.
    Coalesce Lets you update existing target table records when transforming import data.

    You can specify whether a Target field should be used to coalesce on when import set records are transformed. If the field map Coalesce option is selected, when the import set row is transformed, the instance checks for an existing record in the target table that has the same value in the Target field as the import set row Source field.

    If an existing record with a matching value in the target table is found, that record is updated. If no matching record is found, then a new record is created in the target table.

    Supported import formats

    You can import data from several different file formats or external data sources.

    Table 2. File formats
    Format Limitations
    CSV CSV files must comply with the proposed CSV standard in RFC4180. In particular, this means that double quotes may not appear inside fields. The first row of data in an imported CSV file becomes the header row and defines the columns for that import set.
    Excel Excel files must have the XLS or XLSX extension.
    XML XML files must have a consistent XPath for each data row.
    JSON
    • For JSON arrays, the path for each row must specify the array root element twice, such as /incidents/incidents.
    • JSON files should follow RFC-4627. For example, a single comma should separate a value from the following name. Names within an object should be unique.
    • Predicates such as @element, [index], or text(), as well as Axis such as children, siblings, or ancestors are not supported.
    Custom (Parse by Script) You can write custom JavaScript to parse input lines into the list of maps.

    The ServiceNow® platform can import data from the following external data sources.

    Table 3. External data sources
    Data Source Limitations
    JDBC JDBC imports require a valid transform map. Some network configurations may require a ServiceNow® MID Server.
    LDAP LDAP imports require a valid transform map.
    OIDC OIDC imports require a valid transform map.
    ServiceNow® REST (Integration Hub) REST imports require a valid transform map.
    Custom (Load by Script) Custom imports require a valid transform map.

    For more details about the different data sources, see Data sources.

    Transform maps versus robust import set transformers

    Both transform maps and robust import set transformers convert data. Transform maps convert data from the staging table to the destination file. Robust import set transformers convert data from a source table to a message listener. If a data source has both a transform map and a robust import set transformer, the system uses the robust import set transformer instead of the transform map.

    Import set scheduled cleanup

    The Import Set Deleter scheduled job cleans up old import set data every day at midnight.

    By default, the job deletes all import sets that are older than 7 days, as well as the associated import set table records. If this scheduled job is not active on your instance, activate it to mitigate the growing import set tables you may have, especially when using recurring imports.
    Note:
    If scheduled cleanup is not currently active on your system, you must ensure that your import set tables are not too large (> 5 million records total across all tables) before enabling this cleanup. If the tables are too large, manually delete the records first before proceeding. Contact Customer Service and Support to coordinate the deletion of this data.
    Figure 1. Scheduled Script Execution
    Import Set Deleter.

    For procedures, see Delete import sets.

    Import set table deleter

    You can manually delete any or all of the following import set tables:
    • Computer
    • User
    • Notification
    • Location
    You can also delete the related transform maps, or you can delete data only and preserve the table structure.
    Figure 2. Tables available for deletion
    Import set table deleter.

    For procedures, see Delete import set tables.