Understanding Import Sets in ServiceNow

Summary

This instructional video script guides users through the process of importing data into ServiceNow, focusing on the creation and utilization of staging tables. It explains setting up a data source, testing the connection, and observing how ServiceNow automatically generates a staging table based on specified parameters. The script demonstrates loading data into the staging table and introduces the concept of import sets, which help manage records from different import runs, setting the stage for future lessons on moving data to target tables within the ServiceNow platform.

Takeaways

• 📝 The video script is part of a series on importing data into ServiceNow, focusing on the staging table and import set table.
• 🔗 In the previous note, a data source was created, which ServiceNow uses to understand the source for the import and to set parameters for the staging table.
• 📚 The staging table is automatically created by ServiceNow the first time an import is run, based on the parameters set in the data source.
• 🔍 The script demonstrates testing the data source to ensure the connection works and that ServiceNow creates the staging table and loads data into it.
• 📊 The staging table, named 'ustest_import' in the example, is shown to be successfully created with data loaded from an Excel file.
• 📑 The structure of the staging table is displayed, showing custom columns created based on the header row of the imported data.
• 🔄 When an import is run multiple times, ServiceNow manages the records by creating separate entries in the import set table for each import run.
• 🗂️ The import set table, 'sys_import_set', keeps track of each import run and associates records in the staging table with their respective import set.
• 🔗 Each record in the staging table has a reference to the import set it belongs to, showing which import run it was part of.
• 🚀 The next step, to be covered in the next note, is instructing ServiceNow on how to move the staged data into the target table within the ServiceNow platform.

Q & A

What is the primary focus of this note in the series on importing data into ServiceNow?

The primary focus of this note is on the staging table and the import set table that ServiceNow creates as part of the import process.

What is the purpose of creating a data source record in ServiceNow?

The data source record is used by ServiceNow to understand the source it will be connecting to for the import, including what data to pull and parameters for creating the staging table.

What parameters are designated for the creation of the staging table in the data source record?

The parameters include the desired name and label for the staging table.

How does ServiceNow handle the creation of the staging table during the import process?

ServiceNow checks if the staging table exists in the database; if not, it creates it based on the parameters set in the data source and then loads the imported data into it.

What does the 'table name dot list' command do in ServiceNow?

The 'table name dot list' command is used to view the contents of a table in ServiceNow.

Why was the staging table not found during the demonstration in the script?

The staging table was not found because an import had not been run yet, so ServiceNow had not created the table.

What happens when you run an import in ServiceNow for the first time?

During the first import run, ServiceNow checks for the existence of the staging table, creates it if it doesn't exist, connects to the data source, pulls the requested data, and imports it into the newly created staging table.

How does ServiceNow manage multiple import runs and their data in the staging table?

ServiceNow uses an out-of-the-box table called 'sys_import_set' to manage data from different import runs, associating each record in the staging table with a specific import set.

What is the purpose of the 'sys_import_set' table in ServiceNow?

The 'sys_import_set' table represents a group or set of records inserted into the staging table during each import run, helping to manage and organize data from multiple import processes.

What is the next step after creating the data source record and testing the connection in ServiceNow?

The next step is to instruct ServiceNow on how to move the staged data into the target table within the ServiceNow platform, which will be discussed in the next note.

Outlines

00:00

📝 Understanding the Staging Table in ServiceNow Data Import

This paragraph explains the staging table's role in ServiceNow's data import process. It discusses the creation of a data source record in a previous note and how this record informs ServiceNow about the data source for the import. The staging table is automatically generated by ServiceNow based on parameters set in the data source record, including its name and label. The speaker demonstrates testing the data source connection and the creation of the staging table, emphasizing the process of loading data into it. The absence of the table before the first import run and the successful creation and data loading after the import are highlighted.

05:03

🔍 Inspecting the Staging Table and Import Set Management

The speaker continues by examining the newly created staging table, showing its structure and how ServiceNow uses the header row from the data source to label the table columns. The paragraph also touches on the concept of import sets and the management of records within the staging table across different import runs. The 'sys_import_set' table is introduced as a tool for tracking different import runs and associating them with records in the staging table. The demonstration reveals that running the import multiple times results in duplicate records in the staging table, each linked to a specific import set, emphasizing the need for careful record management.

10:03

🚀 Preparing for Data Transfer to Target Tables

In the final paragraph, the focus shifts to the next steps after successfully creating the data source and staging table and verifying the import process. The speaker outlines the process of instructing ServiceNow on how to move the staged data into the target table within the ServiceNow platform, which is the ultimate destination for the imported data. The anticipation is set for the continuation of the series, where the details of this data transfer process will be covered.

Keywords

💡Importing Data

Importing data refers to the process of bringing data from an external source into a system or database. In the context of the video, it is about transferring data into ServiceNow, a cloud-based platform. The script discusses the steps involved in setting up the data source and the staging table for successful data import.

💡ServiceNow

ServiceNow is a cloud-based platform that provides a range of services for IT, customer service, human resources, and other enterprise operations. The video script focuses on how to use ServiceNow for importing data, highlighting its capabilities to handle data sources and staging tables.

💡Staging Table

A staging table is a temporary storage area for data that is being imported into a database. In the script, the staging table is created by ServiceNow during the import process to hold the data before it is moved to the target table. It is crucial for managing and organizing the data load.

💡DataSource Record

A data source record is a configuration within ServiceNow that defines the external source of data to be imported. The script explains how to create a data source record, which includes specifying parameters such as the data to be pulled and the naming of the staging table.

💡Import Set Table

The import set table, or sys_import_set, is an out-of-the-box table in ServiceNow that tracks the groups or sets of records being imported. The script mentions this table to illustrate how ServiceNow manages different import runs and associates records with specific import sets.

💡Table Name

In the context of databases and the script, a table name is the identifier for a specific table where data is stored. The script describes how ServiceNow uses the table name specified in the data source record to create the staging table.

💡Label

A label in ServiceNow is a user-friendly name given to a table or record for easier identification. The script uses the term 'label' to refer to the user-friendly name set for the staging table during the data import setup.

💡Excel File

An Excel file is a type of spreadsheet document created by Microsoft Excel, often used for storing and organizing data. The script mentions an Excel file as the data source from which ServiceNow pulls data during the import process.

💡Test Load

A test load is a trial run of the import process to verify the connection and data transfer without affecting the live system. The script describes using the test load feature in ServiceNow to ensure the data source and staging table are functioning correctly.

💡Record

In the context of databases, a record represents a single row of data within a table. The script discusses records in relation to the data being imported into the staging table and how ServiceNow processes these records during import runs.

💡Import Run

An import run refers to a single execution of the data import process. The script explains how each import run is recorded in the import set table and how it affects the records in the staging table, with each run adding a new set of records.

Highlights

Introduction to importing data into ServiceNow and the importance of the staging table.

The staging table, also known as the import set table, is created automatically by ServiceNow during the import process.

In the previous note, a data source record was created to define the source from which data will be imported.

ServiceNow uses the data source record to understand the data source, pull the specified data, and set parameters for creating the staging table.

A demonstration of using the personal developer instance to view the sys_data_source table.

Explanation of how to view the contents of a table in ServiceNow using the application navigator filter.

Overview of the process for testing a data source connection in ServiceNow.

Steps to test loading records into the staging table from a data source.

ServiceNow automatically checks if the staging table exists before creating it based on the data source parameters.

ServiceNow creates the staging table and loads data when an import is run, showing the successful processing of records.

Description of how to view the structure and layout of a table in ServiceNow.

Staging tables in ServiceNow are extended from the import set row table.

Details on how ServiceNow uses the data source record to create custom columns in the staging table.

Explanation of the difference between staging tables and import set tables in ServiceNow.

The importance of managing records in the staging table based on import runs.

ServiceNow inserts a record into the import set table for each import run, linking records in the staging table to their respective import runs.

ServiceNow allows viewing import set records and their associated staging table records to track data import processes.

Demonstration of running multiple imports and how ServiceNow manages these imports using the import set table.

Details on how to view the records in the staging table and their association with specific import runs.

Final steps to move data from the staging table into the target table within the ServiceNow platform.

Preview of the next steps, focusing on transferring data from the staging table to a target table.

Transcripts

Note 3 in our series of notes on

importing data into servicenow and in

this note

I focused on

the staging table the import set table

that service now creates is part of

the import process if you have been

following along in the series you'll

remember that in the last note

we talked about the creation of a data

source we went in and we created a data

source record

that record is used by servicenow to

understand the source that we will be

connecting to for the import

we tell it in the data source record

what data we would like for it to pull

and then we also designate some

parameters for the creation of the

staging table

including what we'd like that table

named and the label that we would like

set to that table

at this point we are ready to give that

data source a test drive see that the

connection Works see that servicenow

creates the staging table and that the

data gets loaded into the staging table

so

again we're going to pop back into our

personal developer instance that I've

got set here and we're going to review

real quickly what we did in the last

note so we created a data source record

and I know those are stored in the sys

data source table so I'm going to enter

sys

data

Source dot list

anytime you want to see the contents of

a table you can do the table name dot

list in the application navigator filter

here is our import our sorry our data

source that we created in the last note

I'm going to go ahead and open that up

and point out a couple of things for us

to take a look at and remember these two

attributes right here specified for the

import set table and that's the that's

the staging table

this is the label we're using test

import and this is the table name the

actual database table name is U

underscore test underscore import

I mentioned in the last note that

servicenow automatically creates that

staging table as part of the import

process the first time an import is ran

servicenow will check the database to

see if that staging table exists if it's

not it will go ahead and create it based

upon the parameters that we've got set

here in the data source and it will then

load the data that it's pulled on the

import run into that staging table where

it sits and gets ready to be prepared to

actually load into the target so just to

show you I'm going to go ahead and

select the name of this table here

and just like I did before I'm going to

go to my filter Navigator and I'm going

to put the table name dot list

to view the contents of that table

and you're going to see that um

servicenow is telling us well I can't

find that table that table doesn't exist

so we get a little

I guess you'd call it an error message

saying this um this table is not there

so the reason it's not there is we

haven't ran an import yet so we've

specified what we want servicenow to

create it as but we haven't ran an

import and so servicenow hasn't gone in

and actually created that table yet so

that's going to be the next step let's

run an import and see how our data

source works so

go back to our data sources sis

underscore data

Source oops

that list whoops type of type

dot list

I still didn't get it today l i

t

okay there's our data source that we

created in the last note

I'm going to open it up

and you're going to see that once you've

got the data source created and you can

see we've got our file attached here

which is actually our our data source

we're pulling from an Excel file you

have a couple links down here in the

related links area that allows you to

test

um your data source and

load some data so we're going to go

ahead and do that remember if you

remember our little spreadsheet has five

rows

servicenow gives you an option option

here to test load 20 records or load all

the records in the data source in this

case it doesn't matter because 5 is less

than 20. so I'm just going to go ahead

and select load all records

servicenow is now going to go to the

data source

it's actually the first thing it's going

to do is it's going to check to see if

that staging table is there it's going

to see that it's not we just proved that

it's going to create it based upon the

parameters that we have set in our data

source it's then going to connect to the

data source pull the the rows that we've

requested and then import those rows

into that new staging table that it

created and when it's all done we'll get

a nice message here that tells us it

completed successfully and it shows us

that it processed five inserts

um it processed five records and it was

five inserts into our staging table

so very good so we're now going to

return to our

um data source

and I'm going to do what I just did a

moment ago but show you this time that

now that table has been created so our

import set table named utest import

I'm going to pop that in here

and I'm going to do a DOT list

on that table

and there it is we now have the table

staging table created

and we have five rows in the table so

the five rows that it that it found in

our spreadsheet it pulled in and it put

into our staging table

real quickly I'm going to show you what

the staging table looks like the layout

of it

anytime you want to get to a table to

view the actual structure of the table

when you're viewing a list you can um

click on the hamburger menu of any of

the columns and you can go down to

configure

table and it will actually show you the

configuration of that table so what I

really wanted to show you here is yes

indeed it did set the label the way we

ask it to it did set the name the way we

ask it to

um all import set tables or staging

tables extend the import set row table

we'll talk about extensions in another

note but what I really wanted to show

you was these columns these are the

column list the columns with the red X

are custom columns so they're columns

that that weren't out of the box for the

table and you can see that servicenow

used that

um

our header row to label those table

those columns so you can see we've got

an address a city a name a state

and um

let me see how many table together 23

rows here we'll have a zip

we'll have a zip column as well so

servicenow used the data that we had set

in the data source record to create that

staging table and load the five records

into that table

so

we now have data in our staging table

one more thing I wanted to show you to

help understand import sets the staging

table I'm going to go back to the data

source so

um sis

data

Source dot list

I'm going to go into our data source our

test import data source here and I'm

going to run it again so let's say now

we want to run our input we're going to

run our import a second time

um and I'm just going to go ahead and do

load all records again

you can see now that the servicenow uh

successfully came back with a message

saying it was success it processed five

and it inserted five rows so let's go

back

and let's look at our import set

staging table now

go here paste

dot list to see the records and now

we're going to see there are 10 records

here so we've got the five records but

we've loaded them twice

um

and the point I'm making here is that

it's going to be important for us to be

able to manage the records that we load

in our staging table

based upon the import run

that was requested so we actually have

10 rows here but they were actually

added to the staging table in two

separate import runs we did it the first

time we got five we did it the second

time we got five surface now provides an

out of the box table

um

that allows us to manage the data that's

being loaded in our staging table into

the runs or the groups that they belong

to and the name of that table is this is

where it gets confusing the name of that

table is import set and that's why I

refer to the staging table as a staging

table not an import set table because

there actually is an import set table

it's an entirely different thing so

let's take a look at the staging now

import set table this is an out of the

box table from servicenow the name of it

is sys underscore import

underscore set

I'll do a DOT list on that

you're going to see oops we have three

and that is because I didn't clear

something out

um let me see 825 826 826 this one I'm

going to get rid of because it was from

me experimenting yesterday

sorry that makes my demonstration a

little confusing but if I hadn't clear

if I cleared that out from yesterday

um

you can see that we have two records

here uh that were executed uh today and

that was our first import run which was

I set 36 and our second import run which

is I set 37. the way this works is

whenever you run and import the first

thing servicenow does is

um is insert a record into this import

set table

that represents the run or the set of

records that are going to be inserted

into the staging table

so the first time we ran the import we

got this we inserted this row and we got

iset10036 is is the number of that

we then loaded those five records into

the staging table

as they were loaded into the staging

table they all got a value populated in

one of their columns that tied it to

this import set run

so I set 36 was the first one

we then went and ran a second import for

that same data source and servicenow

inserted a second row into this import

set table and it it has a value of 37 as

its number

it then

um went ahead and reloaded the same five

rows into the staging table because

that's what we ask it to do but it

marked each of those with

this second import set reference it's

actually using sysids behind the scenes

to make these connections but talk about

that later

so if I go back now to our staging table

let me go to our data source

back to our data source here

this is our actual staging table

what they call an import set table which

confuses me dot list

the 10 records are here but you can see

there is a set attribute for each of

them and you're going to see that if I

sort by that

there's one two three four five rows

that are tied to the 36 run and there

one two three four five rows that are

tied to the 37 run so this picture on um

in the note attempts to make that kind

of connection and it took me a little

bit to understand the purpose of all

this but the import set out of the box

table

each record represents a group or a set

of records that were inserted into the

staging table

and we can see we have two groups

two records there and then the actual

records in the staging table itself

have a link or reference back to the

import set group that they belong to so

in this case the picture shows three

records with the first import run three

records with the second import run

so at this point we have created our

data source record the tell servicenow

about our source data how to connect to

it what data we want pulled how we want

the um

the staging table named we have then

tested our data source and we've saw

that the connection works we saw that

the staging table was created the way we

asked for it to be we saw that the data

was loaded into

the staging table I mean we validated

that all of that works the way that we

expect

so we have data in the staging table our

next step is to tell servicenow okay

here's how I want you to take this data

that we have staged and actually move it

into the target table which will be a an

existing table in the servicenow

platform where we finally want this data

to be loaded

um and we will be talking about that in

the next note so I'll see you all there