Import Series Part 2 - Anatomy of an Import Set

#Transform Maps , #Import Sets , #Import Data

Josh Nerius

5 minute read

In the first post in this series titled Import Series Part 1 - Getting Started with Import Sets , I set up a simple Import Set to Load and Transform Chicago Park District data into Location records.

In that post, we glossed over some big topics. In this post, we’re going to address:

  • Just what exactly is happening when you use the “Load Data” module?
  • How do all of these pieces fit together?

I’ll address these questions and take a closer look at the things happening behind the scenes.

But First … Why Import Sets?

Something we haven’t discussed yet is why we need import sets to begin with. Here’s the short explanation. Your existing data probably doesn’t exist in a format that perfectly matches your ServiceNow applications. The data may live in a database or external system, column names may not be the same across systems, the format may not match, and in some cases, some records may already exist in ServiceNow.

Import Sets help you handle these scenarios by transforming source data into a format that makes sense and coalescing data to ensure no duplication occurs.

import-set-overview-diagram.png

When building an Import Set, most of your time will be spent on the Transform Map(s) for that Import Set. Here are the main components of a transform map:

  • Field Maps are used to associate a source field with the desired destination field in ServiceNow. For example, you might want data from a “Subject” field in your source data to be stored in the “Short description” field in ServiceNow.
  • Coalesce values merge/reconcile external data with existing records in the system.
  • Scripted Field Maps are used to manipulate incoming data before storing that data in ServiceNow. For example, you might want to split a “Name” field into “First” and “Last” names.
  • Transform Scripts are used to execute scripted logic. This can be used for simple data manipulation or for interacting with multiple tables in ServiceNow. Example: Read a “Group” field from the source data and go create a record in the sys_user_grmember table.

So What DID Happen When We Used the Load Data Module?

The Load Data UI is really just a shortcut that automates a number of tasks. Here are the things that happen when using the Load Data functionality:

  1. Generate an Attachment Data Source and attach the uploaded CSV/XLS file to it
  2. Generate an Import Set Table with fields that correspond to the columns of source data
  3. Generate an Import Set Record to track the current batch of loaded data
  4. Create a navigation Module in the System Import Sets menu that points to the new Import Set Table
  5. Load the source data into the newly generated Import Set Table

Let’s dig into each of these components.

Data Sources

Data Sources are entry points for outside data and allow you to process and load that data into an Import Set Table. There are a number of Types of data sources (see the earlier diagram). In the previous post, I used a File data source with an Attachment file retrieval method. As the name suggests, attachment data sources get their data from a file attached directly to the Data Source record. Files can also be retrieved from from FTP/S, SFTP, HTTP/S and SCP endpoints.

ServiceNow also supports JDBC data sources, but I won’t be discussing those in this post.

Let’s look at the generated data source

Here is a screenshot of the Data Source generated when using the Load Data module. The attachment I uploaded is now attached to the data source, the table name I specified is set and the Type/Retrieval method are File and Attachment.

data-source-generated-by-load-data.png

Now that a data source has been established, we can load records from it. Where exactly do the loaded records go? Into an Import Set Row Table.

Import Set Row Tables

These are temporary staging tables for your data. Data is stored here on a short term basis until it can be transformed (and eventually the records are purged). All Import Set tables extend the Import Set Row base table, and inherit a number of columns that track the status of the record during transformation.

The following screenshot shows a number of import set rows that have already been transformed. You can determine what happened during an import/transform by examining these records after the fact. You will see which target record was created/updated, the raw source data for each row, an error message if something went wrong and more.

blog_iset_part2_import_set_rows.png

Import Sets and Import Set Runs

Each time data is loaded, an Import Set Record is created. This record is a top level “parent” and each source row has a reference to this import set record. This helps track progress throughout the loading and transformation process. Even though there is only one Import Set Row Table for a data source, there may be many Import Set Records and each Import Set can have multiple Runs.

You can examine Import Set Records by navigating to System Import Sets > Advanced > Import Sets.

import-set-run-list.png

Every time you transform an import set, an Import Set Run (also referred to as Transform History) record is generated. All logs and errors (if any) are associated with this run. It is possible to transform the same import set more than once, resulting in more than one Run for that import set.

import-set-run-transform-history.png

Putting it all Together

When all is said and done, there are quite a few moving parts, but generally speaking you’ll only have to worry about:

  1. Data Sources
  2. Import Set Tables
  3. Transform Maps

If you’re really curious, here’s a consolidated look at all of the components related to the Park District Import Set.

Import set Diagram

Summary

Hopefully this gives you some additional insight into what makes an Import Set an Import Set. There’s still a lot to explore, and I’ll dive into Transform Maps in Part 3.

Comments