ServiceNow Data Mart Loader (a.k.a. ServiceNow DataPump)

Quick Start

Overview

This Quick Start Guide contains instructions for installing and testing the ServiceNow DataPump. The Quick Start guide uses a composite JAR file, servicenow-sql-pump.jar created using One-JAR and containing embedded copies of the Oracle and MySQL JDBC drivers, Log4J, JDOM and the sqltemplates.xml configuration file.

This Quick Start Guide assumes that you are using Oracle. For testing I recommend Oracle Express Edition which can be downloaded and installed for free. I also recommend Oracle SQL Developer as a great tool for testing JDBC connectivity and submitting ad-hoc SQL.

Initial Setup

1) Install and Configure Oracle

You will require an Oracle account with CREATE TABLE role. Verify that your user has create table capability by typing in these two commands:

SQL> create table foo (bar varchar2(10), primary key(bar));

Table created.

SQL> drop table foo;

Table dropped.

2) Download servicenow-sql-pump.jar

The file servicenow-sql-pump.jar can be downloaded from the project download page.

3) Configure ServiceNow Web Services access

If you are testing this application using a public demo sandbox instance, then you can use the username "admin" with the password "admin". Otherwise you will need to ensure that Web Services are enabled for your instance and you will need to create a role and a user for the application.

  1. Create a role named "datapump". The role must contain the following roles:
    • soap_query
  2. Grant the datapump role read access to the following tables:
    • sys_db_object
    • sys_dictionary
  3. Ensure that the datapump role has read access to any tables you intend to replicate. Be sure to check for field level Access Controls which could interfere with operation.
  4. Create a ServiceNow user named "soap.datapump". The password will be specified in the datapump.properties file.

5) Create datapump.properties

Create a text file named datapump.properties with seven lines as follows. Provide your ServiceNow and Oracle connection parameters. The "oracle2" dialect from sqltemplates.xml is recommended since it is the most thoroughly tested. 

# ServiceNow connection parameters
servicenow.url=https://demo001.service-now.com/
servicenow.username=admin
servicenow.password=admin
# Database connection parameters
pumper.sql.url=jdbc:oracle:thin:@localhost:1521:xe
pumper.sql.username=scott
pumper.sql.password=tiger
# Dialect controls which section of sqlconfig.xml will be used
pumper.sql.dialect=oracle2

6) Create a simple script

Create a file named load_location.txt with a single line as follows:

load cmn_location

7) Execute the DataPump

java -jar servicenow-sql-pump.jar -p datapump.properties -f load_location.txt

At this point the application should create a table named cmn_location in your Oracle database, and copy into it the contents of the ServiceNow cmn_location table. If you run the application a second time it will detect that the Oracle table already exists. It will match field names between the ServiceNow and Oracle tables, and update or insert records using sys_id.

If you have gotten this far successfully, then proceed to the Scripts to read about more loading options.