Data Source

What you will learn from this module?

• Create a Data Source for importing records
• Load data into a staging table
• Create a Transform Field Map
  • Auto Map Matching Fields
  • Mapping Assist
• Change or set field values using a Transform Map script
• Coalesce to detect collisions
• Use a Transform Event script to update but not insert records

Preparing Data for Import

Before importing data into ServiceNow, it is recommended you take the time to:

• Understand what data you are bringing in
• Decide what to do with incomplete or erroneous data
• Create a plan to map the source file columns to the target table columns

Data Sources

Data sources define what data should be imported. Only admin users can create data sources.

Data sources:

• CSV
• JDBC
• FTP
• HTTP
• XML

Navigate to System Import Sets > Administration > Data Sources to create a new data source record.

Data Import Process

Data is not imported directly from a Data Source into the target table. The steps are:

• Load data into a staging table
• Create a Transform Map
• Run a Transform to move data from the staging table to the target table
• Check the data integrity

Load Data

Navigate to System Import Set > Data Source and open the newly created data source.

Attached excel sheet and click on related link “Test Load 20 Record”

Create a Transform Map

A Transform Map matches the columns from the staging table to the columns in the target table. Every import operation requires at least one transform.

• Configure the Table Transform Map:
  • Navigate to System Import Set > Data Source and open the newly created data source.

Go to related list named “Transform” and click on “New” button to create a new transform map.

Click on Auto Map Matching Fields related link to auto map the fields or click on Mapping Assist to manual field map.

Update coalesce filed to True for an attribute so that if system will look for the record based on coalesce field value. It will update if find the match else create new record.

Run a Transform Map

Running a transform map imports data from the staging table into the target table. To run a Transform Map, click the Transform Related Link in the Table Transform Map record.

In the Specify Import set and Transform map form, verify that the Transform Map to run is in the selected maps, run in order slush bucket. Click the Transform button.

The Progress page shows the transform’s State and Completion code. A Completion code value of Success does not mean the records imported correctly. The Completion code indicates whether the transform executed successfully and is not indicative of the data integrity of the records.

Click the Import log link to see if there were errors with the data. In the example, the transform and import was successful. 

Click on Transform History to see the state like how many got inserted/updated/skipped/Ignored/Errors

Transform Event Scripts

Transformation events occur during the process of transforming an import set table onto a target table. Transformation Event Scripts modify the transformation behavior at different points in the transformation process.

To create a Transform Event Script, switch to the Transform Scripts related list in a Transform Map and click the New button.

The When option in the Transform Script trigger specifies when in the transformation process the script should run.

The When field choices are:

• onStart: executes at the start of an import before any rows are read
• onAfter: executes at the end of a row transformation and after the source row has been transformed into the target row and saved
• onBefore: executes at the start of a row transformation and before the row is transformed into the target row
• onChoiceCreate: executes at the start of a choice value creation before the new choice value is created
• onComplete: executes at the end of an import after all rows are read and transformed
• onForeignInsert: executes at the start of the creation of a related, referenced record before the record is created
• onReject: executes during foreign record or choice creation if the foreign record or choice is rejected. The entire transformation row is not saved.

When coalescing, the requirement might be to update only records that match and not insert any new records. To meet the update records only requirement requires an onBefore Transform Script. The onBefore script executes after ServiceNow has determined whether there is a matching record in the target table and before the insert happens

The action string variable is automatically created. It has two possible values: insert and update. The action variable is set after coalescing determines if there is a match (update) or no match (insert).

The ignore Boolean variable is automatically created. When true, the ignore variable stops the transformation process for the source data row.

Objects are in Transform Script

source	GlideRecord	The row of the source table that is currently being processed.
target	GlideRecord	The row of the target table that is currently being processed.
import_set	GlideRecord	The import set that is currently being transformed.
map	GlideTransformMap	Read-only information about the current transform map record.
log	Function	The log object for the current import run. For example, log.info(…), log.warn(…), log.error(…).
action	String	Action returns a value of either “insert” or “update” indicating whether the current target row was created or updated.
status_message	String	Defines a custom message to be sent in the <status_message> XML response.
error	Boolean	When set to true, will halt the entire transformation for the current import set, with an error message.
error_message	String	Defines a custom message to be sent in the <error_message> XML response.