Data Import Using JDBC Data Source from a Third-Party Server

Shiva Charan · 2 hours ago

Overview

This article explains how RMI Account data is imported into ServiceNow using a JDBC Data Source from a third-party SQL Server. The solution uses a Scheduled Data Import, JDBC Data Source, Transform Map, and a Script Include to handle account relationships and account team members efficiently.

The design ensures:

Incremental data loads
Automated execution
Clean and maintainable logic

Use Case

Import account data from an external RMI system
Use MID Server–based JDBC connectivity
Process only updated records
Automatically assign Internal Account Managers to accounts
Maintain parent–child account relationships

Prerequisites

MID Server configured and validated
JDBC connection to the third-party SQL Server
Import user with import_admin role
Required custom tables and fields created

Solution Architecture

Scheduled Data Import
JDBC Data Source
Import Set Table
Transform Map with OnAfter Script
Reusable Script Include

1. Scheduled Data Import Configuration

Scheduled Import Details:

Field Value

Name	RMI Import Account
Data Source	RMI Import Account
Run As	RMI import (User with import_admin role)

Post Script

The post script dynamically updates:

Last run timestamp
SQL query execution parameter

This ensures only incremental data is fetched during each run.

(function() {
    var gd = new GlideDate();
    data_source.last_run_datetime = gd.getValue();
    data_source.sql_statement = gs.getMessage(
        'rmi_import_accounts.set_sql_statement',
        [gd.getValue() + ''],
        ""
    );
    data_source.autoSysFields(false);
    data_source.setWorkflow(false);
    data_source.update();
})();

2. JDBC Data Source Configuration

Data Source Details:

Field Value

Name	RMI Import Account
Type	JDBC
Import Set Table	u_rmi_import_account
MID Server	MK1devMIDSN02
Database Type	SQL Server

Connection Details:

Server: example.live.iscloud.example.net
Database Name & Port: Configured as per environment

SQL Query Used

EXEC ExtServiceNowAccount 
     @Full_Extract = 0, 
     @Last_Updated = '2025-12-30',
     @Rows = 0, 
     @Debug = 0

Key Points:

Uses stored procedure
Supports incremental extraction
Optimized for performance

3. Transform Map Configuration

Transform Map Name: RMI Import Account

Source Table: u_rmi_import_account
Target Table: customer_account
Field mappings configured as required

OnAfter Transform Script

This script assigns Internal Account Managers to the account after successful transformation.

(function runTransformScript(source, map, log, target) {
    if (source.u_int_acc_man_employee_no && target.sys_id)
        new RMiImportUtis().processAccountTeamMembers(
            target.sys_id,
            source.u_int_acc_man_employee_no,
            'Internal Account Manager'
        );
})(source, map, log, target);

4. Script Include (Required Logic Only)

The Script Include contains reusable utility methods.
Below are only the required methods used for account imports.

Script Include: RMiImportUtis

a) Populate Parent Account Relationships

updateParentAccount: function(parentAccountNo, reprocessArray) {
    var parentAccount = new GlideRecord('customer_account');
    if (parentAccount.get("u_rm_account_number", parentAccountNo)) {
        var targetAccount = new GlideRecord('customer_account');
        targetAccount.addQuery('sys_id', 'IN', reprocessArray);
        targetAccount.query();
        targetAccount.setValue('account_parent', parentAccount.sys_id);
        targetAccount.updateMultiple();
    }
}

b) Process Account Team Members

This method assigns or updates Internal Account Manager responsibility on the account.

processAccountTeamMembers: function(account, employeeNumber, responsibility) {

    var getUserByEmpNumber = function(empNo) {
        var sys_user = new GlideRecord('sys_user');
        return (sys_user.get("employee_number", empNo)) ? sys_user.sys_id : "";
    };

    var getResponsibilityDef = function(name) {
        var responsibility_def = new GlideRecord('sn_customerservice_responsibility_def');
        responsibility_def.addQuery('type', 'sys_user');
        responsibility_def.addQuery('name', name);
        responsibility_def.query();
        if (!responsibility_def.next()) {
            responsibility_def.initialize();
            responsibility_def.name = name;
            responsibility_def.type = "sys_user";
            return responsibility_def.insert();
        }
        return responsibility_def.sys_id;
    };

    var user = getUserByEmpNumber(employeeNumber);
    if (user) {
        var team = new GlideRecord('sn_customerservice_team_member');
        team.addQuery("account", account);
        team.addQuery("user", user);
        team.query();
        if (team.next()) {
            team.responsibility = getResponsibilityDef(responsibility);
            team.update();
        } else {
            team.initialize();
            team.account = account;
            team.user = user;
            team.responsibility = getResponsibilityDef(responsibility);
            team.insert();
        }
    }
}

End-to-End Flow

Scheduled Import runs automatically
JDBC Data Source executes stored procedure
Data loads into import set table
Transform Map creates/updates accounts
OnAfter script assigns account managers
Parent account relationships are updated

Benefits of This Approach

Fully automated and scalable
Supports incremental data loads
Clean separation of logic using Script Include
Minimal hardcoding
Easy to maintain and extend

Best Practices Followed

MID Server–based JDBC connectivity
Reusable Script Include methods
Post-script driven incremental extraction
Workflow and sys field updates disabled during import
Optimized SQL execution

Conclusion

This solution provides a robust and maintainable approach to importing RMI Account data into ServiceNow using a JDBC Data Source. By combining scheduled imports, transform logic, and reusable utilities, the integration ensures data accuracy, performance, and long-term scalability.