ServiceNow Import Set API

Post to /api/now/import/{staging_table} when the sending system can push but you still want ServiceNow transform maps, coalescing and field mapping to control what lands in the target table.

The distinction that decides whether this API is the right choice is the staging table. Data arrives in a table that is not the target, and a transform map moves it across. That indirection is the whole point: it gives you a place to map field names, look up references, coalesce on a key the sender knows, and keep the raw payload for investigation after something goes wrong. If none of that is needed, the indirection is cost without benefit.

The API runs the transform immediately and returns the resulting record sys_id and status. That makes it useful for integrations that need to know the outcome, unlike a scheduled import.

The cost of that immediacy is that the sender waits while the transform runs, so anything slow in the map becomes latency the other system feels. A lookup against a large table, a script that queries in a loop, or a business rule on the target that does real work all land in the caller's response time. This is the main reason the advice to keep transform maps simple is operational rather than stylistic.

It also sets a practical limit on batch size. A payload of several thousand rows transforms row by row inside one request, and either the caller times out or the connection is held open long enough to be a problem. Smaller batches, posted more often, behave far better and fail in smaller pieces when they fail.

Staging tables grow fast.

• Schedule cleanup of old import sets and rows
• Keep the transform map simple, complex scripts here are hard to debug at volume
• Return errors to the caller rather than swallowing them
• Use a dedicated integration user so volume is attributable

Two more operational points worth settling before go live. Decide how long staging rows are kept, because they are your only record of what a sender actually sent, and a week of retention turns most integration arguments into a lookup. And decide who is told when rows fail, because a transform that rejects rows quietly is indistinguishable from an integration nobody is using.

Worth walking the path once, because every operational problem with this API sits at one of its steps.

1. The sender posts a JSON payload to the endpoint for a named staging table.
2. A row is inserted into that staging table, and an import set record groups it.
3. The transform map for that staging table runs immediately.
4. Coalescing decides whether this is a new target record or an update to an existing one.
5. Field maps and any transform scripts populate the target record.
6. The response comes back with the target table, the sys_id and the status for each row.

The design point is step four. Coalescing is what makes the API safe to call twice with the same data: a coalesce field, or a set of them, identifies the target record, so a repeat post updates rather than duplicates. An integration without coalescing configured creates a new record every retry, and retries are normal.

Where it lives: staging tables descend from sys_import_set_row, the batches are in sys_import_set, and the maps are in sys_transform_map with their field maps alongside. All four are worth knowing by name because that is where you look when something did not arrive.

Half an hour on a developer instance, and it makes the failure modes visible.

1. Create a staging table with three fields and a transform map onto incident, coalescing on a correlation identifier.
2. Post one record with a REST client. Read the response: note the sys_id and the status word.
3. Post the identical payload again. The status should say updated rather than inserted. If it says inserted, coalescing is not configured the way you think.
4. Now post a payload with a value that will not fit the target field, or a reference that does not resolve. Read what comes back.
5. Open the import set row. Its state and the transform log say what happened at each step.
6. Check the target record and confirm nothing half formed was created.

Step four is the one to spend time on, because it is where integrations differ from each other. What comes back on a bad row decides whether the sending system can do anything useful about it.

The synchronous behaviour is the reason to use this API, so the error contract is the part worth designing rather than accepting.

Three outcomes need to be distinguishable by the caller. Accepted and transformed, with the target sys_id, so the sender can store the link. Rejected for a reason the sender can fix, such as a bad reference or a missing mandatory value, which should be reported with enough detail to correct and resend. Failed for a reason the sender cannot fix, such as the instance being unavailable, which should be retried rather than corrected.

The common mistake is a transform script that catches its own errors and completes anyway. The response says success, the target record is wrong or missing, and the sending system has no idea. Swallowing errors here converts a loud failure into a silent one, which is always the worse trade.

The related decision is what to do about partial batches. If a payload carries several rows and one fails, the others still transform, so the caller has to handle a mixed result rather than a single status. Deciding that deliberately is better than discovering it in production.

This API is one of several ways in, and the choice matters.

The Table API writes straight to the target table. Simpler, faster, and no transform, so use it when the sender already speaks your data model and nothing needs mapping.

Scheduled imports pull from a file or a data source on a timer. Right for bulk, wrong when the sender needs to know the outcome.

IntegrationHub and flows when the integration involves orchestration rather than a single write.

The import API earns its place in exactly one situation: the sender pushes, the data needs mapping or coalescing, and the sender needs the result immediately. Outside that, one of the others is simpler.

Version note: the endpoint and its behaviour have been stable for many releases, while the surrounding tooling has not. Robust transport, IntegrationHub spokes and flow based integrations have all arrived since this pattern became common, and on a current instance a spoke may do the whole job with no staging table at all. Check what is available before building this by hand. See the Table API and catalog variable sets.

• No coalesce field. Every retry creates a new record.
• Transform scripts that swallow errors. The caller is told success.
• No cleanup on staging tables. They grow without limit and nobody notices until storage does.
• A shared account for the integration. Volume and errors cannot be attributed to anybody.
• Complex logic in the transform. It is the hardest place in the platform to debug at volume. See troubleshooting import sets and update sets.

Posting a record and watching it transform takes half an hour, and designing an integration whose errors reach somebody who can act on them is the work the course covers.