insertMultiple Import set API

[ytrottier]

Sharing this information for everyone to know.

After working with SN on a case about this subject, here is the case summary :

What I understand from your replies is that you have verified with the development team and there is no way OOTB of managing the insertMultiple Import set API response to include any meaningful feedback about the import itself.

The 2 documented script variables that are expected to write to the API call SOAP response (status_message and error_message - from https://docs.servicenow.com/bundle/sandiego-platform-administration/page/script/server-scripting/ref...) cannot be used in combination with the onComplete script to get the meaningful feedback we are looking for.

That is obviously a significant gap in the usability of that API.
It forces everyone to make multiple API calls to first do the import and then go get the details of the execution.

That said, here are the detailed steps of the workaround that is required for the the insertMultiple Import set API since meaningful feedback is not available OOTB - and again, certainly, it should be :

1- The insertMultiple Import set API is asynchronous by default and does not provide any meaningful feedback.
To get the expected results at the end of the import, set a synchronous transformation by creating a new record in the Rest Insert Multiples [sys_rest_insert_multiple] table, selecting the source table (import set table), and setting the transformation to synchronous.
Also create within the Rest Insert Multiples record a related Column mappings where the Column mapping is Column name (the default is Label).

2- Use the insertMultiple Import set API (POST /now/import/{stagingTableName}/insertMultiple) to insert records into the target table.
Using the JSON data array provided within the insertMultiple Import set API call (see the doc for details : https://docs.servicenow.com/bundle/sandiego-application-development/page/integrate/inbound-rest/conc...), SN will write to the import set table, and run the related transformation to save the records into the target table (2 reads and 2 writes per record).

3- With the import defined as synchronous, it will only complete at the end of the import, even if 1000s of records are to be loaded from the JSON data array.
On completion, the actual result of the import is available in the Transform History table (sys_import_set_run), using the Table API, where the Set field = the returned import_set_id from the insertMultiple Import set API call.
(GET https://xxxxxx.service-now.com/api/now/table/sys_import_set_run?set=[import_set_id from the insertMultiple Import set API call])
If you want to try it out using the REST API Explorer Table API, you will need to update glide.ui.permitted_tables property by adding ",sys_import_set_run, sys_import_set_row_error" to its table list, since by default, system tables (tables beginning with "sys_") are not reportable or accessible by the the REST API Explorer.

4- From the Transform History table response, looking at the [total, inserts, updates, processed, ignored, skipped, and errors] fields, logic will decide if some errors occurred. And if there are, we can get the errors details from the Import Set Row Errors (sys_import_set_row_error) table, using the Table API, where the Run history field = the returned sys_id from the previous Transform History table GET call.
(GET https://xxxxxx.service-now.com/api/now/table/sys_import_set_row_error?sysparm_display_value=true&sys... from the previous Transform History table GET call])
Another option is to always do the Import Set Row Errors (sys_import_set_row_error) Table API call, and if the response X-Total-Count (result row count) is 0, then, there is no error.

Wow. That is quite an involved process.
Clearly that should all be OOTB.

There is an Enhancement Request in the Idea Portal about it : https://support.servicenow.com/ideas?id=view_idea&sysparm_idea_id=6aa1a464db225d5039445ac2ca961940&s...
Feel free to go there and vote for it.

[Martin Fucik]

Great work. Thanks for sharing. Very helpful.

[Dave Oshinsky]

That is really helpful, adding a lot of detail that is missing from the Import Set API documentation. I'm hoping that ServiceNow corrects the vague, incomplete documentation here regarding the staging table (and sys_transform_map / sys_transform_entry tables).

I'm trying to understand the prerequisites for successfully running the Import Set API, specifically regarding the staging table. If I create a staging table manually with fields matching the fields in the JSON to be imported, I see this error when I try the run the insertMultiple Import Set API:

Response code 400 value '{"error":{"message":"Invalid staging table","detail":"Specified table does not extend 'sys_import_set_row'"},"status":"failure"}'

Based on the error, it appears that a manually created staging table must extend sys_import_set_row. I then re-created the staging table to extend sys_import_set_row, and added columns matching those in the JSON to be imported. The import now completes (using the "synchronous" setting from a sys_rest_insert_multiple row). The sys_import_set_run table shows that the import did 2 inserts. 2 records were indeed inserted into both staging and target tables. But none of the import JSON field values appear in either staging or target table.

Can someone please clarify the requirements on the staging table?

I should add that I populated sys_transform_map and sys_transform_entry rows to provide a transform map, and a field map in sys_transform_entry covering all of the fields in the JSON to be imported. The requirements on these sys_transform_map and sys_transform_entry rows also appear to be undocumented.

[reg_1979]

Hi Dave, 

I had a similar issue but the solution was in ytrottier's response above. I believe you need to add a record to the REST Insert Multipes table [sys_rest_insert_multiple] and then add a columns mapping entry underneath that. I used column names in my mapping. Once I added these entries my json values passed to the import set table. Hope this helps!