Troubleshooting ServiceNow Data Migration and Import Sets: A Practical Guide

Understanding import architecture and data flow

ServiceNow Data Migration and Import Sets provide a framework for loading external data into ServiceNow tables. The flow is: external data source → Staging Table (Import Set) → Target Table (through a Transform Map). Data is extracted from the source, loaded into the Import Set table, then transformed and written to the target table. Solution Architects troubleshooting failed imports must understand this flow. Problems can occur at any stage: data may not be extracted correctly, the import may fail due to validation errors, or the transformation may map fields incorrectly. Identifying which stage failed determines the fix.

Source data extraction and loading

Data extraction brings external data into ServiceNow. The source might be a CSV file, an Excel spreadsheet, a database query, or an API. Common extraction issues: the source file format is invalid, the file character encoding is wrong, or the extraction query times out on large datasets. If external data does not appear in the Import Set table, investigate the extraction. For file uploads, verify the file is the expected format (CSV must be comma-delimited, Excel must be the expected sheet). For database queries, ensure the query returns data. Check the staging table to see if any rows were loaded. If the table is empty, the extraction failed. Check import logs for error messages. A common error: the source file lacks expected columns or column names do not match the Import Set table schema. Verify the source data structure matches expectations before importing.

Import Set table definition and validation

The Import Set table is a temporary table that mirrors the source data structure. It has a schema with columns matching the source. Validation occurs at this stage: data types must be correct, required fields must be present, and row counts must match expectations. If the Import Set table does not have columns for all source data, source data is lost during import. Define the Import Set table schema to match all source columns, even if some columns are not transformed to the target table. This ensures complete data capture. If validation fails—for example, a column has a numeric value but the source indicates text—investigate whether the source data type is correct. Sometimes source systems provide numeric values as text strings that cannot be converted.

Field mapping and transformation errors

The Transform Map links Import Set columns to target table columns. A simple transform maps Import Set field A directly to target table field B. Complex transforms use scripts or conditional logic to combine multiple source fields or derive values. When target table records are missing expected data, the transform map is usually the culprit. A field is not being mapped, or the mapping references the wrong source column. Query the target table to see what was written. If a field is null when it should have data, the transform is not providing a value for that field. Check the Transform Map: is the target field included in the map? If not, add it. Is the source field correct? Verify the mapping by examining a few imported records side-by-side with source data. Does the target field value match the source field value? If not, the mapping or a transform script is modifying the data unexpectedly.

Duplicate detection and record matching

When importing data for an existing table, the system must decide whether to create a new record or update an existing one. This is handled through duplicate detection. The Transform Map specifies matching criteria: match on a specific field like ID or email address. If records match, the existing record is updated. If no match is found, a new record is created. When duplicate detection does not work—the system creates duplicate records for data that already exists—investigate the matching criteria. Is the matching field populated in both the source and target? If the source data lacks IDs but the matching criteria looks for ID matches, the match will always fail. Ensure the matching field has values in all records. Also check data quality: if email addresses have different capitalization or whitespace, matches may fail even though the records represent the same entity. Normalize source data before import to ensure matching works correctly.

Batch imports and error handling

Large imports process thousands or millions of records. Rather than importing all at once, batch the import into smaller chunks. If an error occurs, it affects only the current batch, not the entire import. Configure batch size based on available memory and processing power. Monitor batch processing to identify which batches failed. If a batch fails, investigate the error, fix the issue, and re-import that batch. Some batches may have data quality issues that prevent import: invalid dates, missing required fields, or constraint violations. For these batches, you must either fix the source data and re-import, or create records manually. Logging is critical: the system must record which batches succeeded and which failed so you do not accidentally re-import successful batches or miss failed batches.

Post-import validation and reconciliation

After import completes, verify that all expected data was loaded correctly. Compare record counts: how many rows were in the source, how many were imported, and how many errors occurred? Query the target table to spot-check imported records. Do they have correct values? Are numeric fields numeric? Are dates formatted correctly? Use a reconciliation report to identify discrepancies between source and target. If records are missing or have incorrect values, diagnose the cause: was data lost during transformation, was there a validation error, or was data not in the source? Use the import logs and audit trail to trace what happened to each record. Document any data that could not be imported and plan manual remediation if necessary.

Matching the symptom to the stage that most likely failed

When a load goes wrong, the people in the room often start with the error they can see, which is often the least useful clue. A row count mismatch looks like a transform fault, but it is frequently a parsing problem upstream, where a delimiter inside a free-text field split one record into two. The table below starts from what the administrator observes and points to the stage to inspect first. It is a starting point for the investigation, not a diagnosis.

An ordered diagnostic sequence for a failed run

Work through the stages in the order data travels, and stop as soon as one check fails. Jumping to the transform map first is a common way to lose an hour on a problem that began in the source file.

1. Confirm the source file or query produced the expected number of rows, and that its encoding and delimiter match what the import set expects.
2. Inspect the first and last rows in the staging table against the source, looking for truncation, shifted columns and quoted values that were split.
3. Review the import log for rejected rows, grouping them by error text rather than reading them one at a time.
4. For a sample of rejected rows, separate data faults from mapping faults, and fix the data where the data is at fault.
5. Re-run only the failed batches, and confirm that the successful batches are not loaded a second time.
6. Run the reconciliation counts again and compare the result with the staging count, not with the file on disk.

Two problems that look like performance issues

Long-running imports are often blamed on the platform when the cause is a transform script that performs a lookup for every row. Moving the lookup into a cached reference or a single pre-load join often cuts the run time sharply. If the platform itself is under load, check the scheduling of the import against other jobs, since an import that competes with reporting jobs will look slower than it is. The platform performance guide covers how to separate those causes.

How do I stop a bad import from repeating on retry?

Keep the batch identifier in every row and make the retry process select failed batches by identifier, not by time. A retry that selects by date will load successful rows a second time, and the duplicates are harder to remove than the original fault.

Why do my match rules work in testing but not in production?

Production source data often has trailing spaces, mixed capitalization and occasional leading zeros that the test extract did not. Normalize the match field in the staging step, and test the normalization on the messiest rows you have, not the cleanest.

Should I edit the import set table directly to fix a row?

Only as a last resort for a single row that will not be loaded again. Any direct edit must be recorded, because the next reconciliation will otherwise show a difference that nobody can explain.

For the reconciliation side of the same problem, the migration reconciliation controls guide explains which counts are worth keeping, and the transform map design guide covers the design of the mapping rules that failed runs often trace back to.