Bulk Data Sync via Import Sets Pattern…

intermediateTested on Xanadu

The Bulk Data Sync via Import Sets pattern provides a robust, scalable approach for importing large volumes of external data into ServiceNow tables. This pattern leverages ServiceNow's Import Set framework to stage data in temporary tables before transforming it into target tables, enabling data validation, error handling, and rollback capabilities.

This pattern solves the challenge of reliably importing data from external systems while maintaining data integrity and providing visibility into import success and failure rates. The Import Set framework handles data type conversions, field mapping, coalescing logic, and provides detailed logging of transformation results.

The pattern works by first creating an Import Set staging table that mirrors the structure of incoming data, then defining Transform Maps that specify how staged data maps to target ServiceNow tables. Data flows through three stages: initial import to staging table, transformation via Transform Map rules, and final insertion or update of target records with comprehensive error tracking.

Chrome Extension

Sourdough: ServiceNow Monitoring and Analytics

A Chrome extension for ServiceNow Admins and Developers with essential tools, analytics, graphs and monitoring features.

Instance HealthGraphs & ChartsAPI HealthDeveloper ToolsQuick SearchInstance Switcher

Add to Chrome

Free to install. Pro $5/month after a 14-day no-card trial.
Pro requires the ServiceNow admin role. Upgrade inside the extension.

Overview

Tasks

CMDB

API

Metrics

Monitor

Internals

Instance:sourdoughdev·Version:Yokohama

Instance StateONLINE

System StatusFully Operational

Session Timeout90 minutes

Logged-In Sessions2 (20 active)

Build Nameyokohama-12-18-2024_p1

IP Address10.159.128.43

Instance HealthHealth Score: 90%

🔥 5dSourdough (Chrome Plugin)Dark Mode

Use This When

Use this pattern when importing large datasets from external systems on a scheduled basis, when you need detailed error reporting and data validation during import processes, or when importing data that requires complex field mapping and coalescing logic. This pattern is ideal for nightly data feeds, bulk user imports, or asset synchronization from external CMDBs.

Avoid When

Avoid this pattern for real-time integrations requiring immediate data synchronization, as Import Sets are designed for batch processing. Don't use for simple single-record imports where direct table inserts would be more efficient, or when you need bidirectional data synchronization as Import Sets are primarily designed for inbound data flows.

Implementation

Server-side

var BulkDataSyncUtil = Class.create();
BulkDataSyncUtil.prototype = {
    initialize: function() {
        this.importSetTable = 'u_emp_import_staging';
        this.transformMap = 'Employee Import Transform';
    },

    // Main method to process bulk data import
    processBulkImport: function(dataArray) {
        var importSetSysId = this._createImportSet();
        var insertedCount = 0;
        var errorCount = 0;

        // Stage data in import set table
        for (var i = 0; i < dataArray.length; i++) {
            try {
                this._insertToStagingTable(dataArray[i], importSetSysId);
                insertedCount++;
            } catch (e) {
                gs.error('Failed to insert staging record: ' + e.message);
                errorCount++;
            }
        }

        gs.info('Staged ' + insertedCount + ' records, ' + errorCount + ' errors');

        // Execute transform map
        var transformResult = this._executeTransform(importSetSysId);
        return {
            importSetId: importSetSysId,
            stagedRecords: insertedCount,
            stagingErrors: errorCount,
            transformResults: transformResult
        };
    },

    // Create new import set run
    _createImportSet: function() {
        var importSetGr = new GlideRecord('sys_import_set');
        importSetGr.initialize();
        importSetGr.state = 'loaded';
        importSetGr.table_name = this.importSetTable;
        return importSetGr.insert();
    },

    // Insert data into staging table
    _insertToStagingTable: function(dataRow, importSetSysId) {
        var stagingGr = new GlideRecord(this.importSetTable);
        stagingGr.initialize();
        stagingGr.sys_import_set = importSetSysId;
        
        // Map external data fields to staging table columns
        stagingGr.u_employee_id = dataRow.empId || '';
        stagingGr.u_first_name = dataRow.firstName || '';
        stagingGr.u_last_name = dataRow.lastName || '';
        stagingGr.u_email = dataRow.email || '';
        stagingGr.u_department = dataRow.department || '';
        stagingGr.u_manager_id = dataRow.managerId || '';
        
        return stagingGr.insert();
    },

    // Execute transform map and return results
    _executeTransform: function(importSetSysId) {
        var transformer = new GlideImportSetTransformer();
        var transformMapGr = new GlideRecord('sys_transform_map');
        
        if (!transformMapGr.get('name', this.transformMap)) {
            throw new Error('Transform map not found: ' + this.transformMap);
        }
        
        transformer.setTransformMap(transformMapGr.getUniqueValue());
        transformer.setImportSet(importSetSysId);
        
        var result = transformer.transform();
        
        // Get detailed results
        var results = this._getTransformResults(importSetSysId);
        gs.info('Transform completed - Inserted: ' + results.inserted + 
                ', Updated: ' + results.updated + ', Errors: ' + results.errors);
        
        return results;
    },

    // Analyze transform results from import set rows
    _getTransformResults: function(importSetSysId) {
        var results = {
            inserted: 0,
            updated: 0,
            errors: 0,
            errorMessages: []
        };
        
        var stagingGr = new GlideRecord(this.importSetTable);
        stagingGr.addQuery('sys_import_set', importSetSysId);
        stagingGr.query();
        
        while (stagingGr.next()) {
            var status = stagingGr.getValue('sys_import_state');
            switch (status) {
                case 'inserted':
                    results.inserted++;
                    break;
                case 'updated':
                    results.updated++;
                    break;
                case 'error':
                    results.errors++;
                    results.errorMessages.push(stagingGr.getValue('sys_import_state_comment'));
                    break;
            }
        }
        
        return results;
    },

    // Scheduled job method for automated imports
    runScheduledImport: function() {
        try {
            // Fetch data from external source (REST call, file read, etc.)
            var externalData = this._fetchExternalData();
            
            if (externalData && externalData.length > 0) {
                var result = this.processBulkImport(externalData);
                
                // Send notification for significant errors
                if (result.transformResults.errors > 10) {
                    this._sendErrorNotification(result);
                }
            }
        } catch (e) {
            gs.error('Scheduled import failed: ' + e.message);
        }
    },

    // Mock method - replace with actual data source integration
    _fetchExternalData: function() {
        // Example REST call to external system
        var request = new sn_ws.RESTMessageV2();
        request.setHttpMethod('GET');
        request.setEndpoint('https://external-hr-system.com/api/employees');
        request.setRequestHeader('Authorization', 'Bearer ' + gs.getProperty('hr.api.token'));
        
        var response = request.execute();
        if (response.getStatusCode() == 200) {
            var responseBody = response.getBody();
            return JSON.parse(responseBody);
        }
        return [];
    },

    _sendErrorNotification: function(result) {
        gs.eventQueue('import.errors.high', null, result.importSetId, 
                      JSON.stringify(result.transformResults));
    },

    type: 'BulkDataSyncUtil'
};

The code demonstrates a complete Import Set workflow with staging table management, transform execution, and comprehensive error tracking. Key aspects include the separation of data staging and transformation phases, detailed result analysis that tracks insert/update/error counts, and integration points for external data sources and error notifications.

Alternatives

Direct Table API Integration

Use for real-time single-record updates where immediate processing is required and you don't need the staging and validation benefits of Import Sets, but loses built-in error handling and rollback capabilities.

Integration Hub ETL Flows

Better for complex data transformations requiring joins across multiple external systems and visual workflow management, but requires Integration Hub licensing and is less suitable for simple bulk imports.

Scripted REST API with Batch Processing

Ideal for external systems that need to push data in real-time batches with custom authentication and validation logic, but requires more custom error handling and lacks Import Set's built-in coalescing features.

Test Your Knowledge

Quick 3-question quiz — see how your ServiceNow skills stack up.

Question 1 of 3Performance

A list view on a table with millions of records is slow. Best fix?

Select an answer to continue