Understanding Import Sets in ServiceNow
Summary
TLDRThis 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
📝 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.
🔍 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.
🚀 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.
Mindmap
Keywords
💡Importing Data
💡ServiceNow
💡Staging Table
💡DataSource Record
💡Import Set Table
💡Table Name
💡Label
💡Excel File
💡Test Load
💡Record
💡Import Run
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
5.0 / 5 (0 votes)