Import Set API - multiple records

[PhoenixMing0912]

Hi all,

I have built an import set API and am tring to update multiple records.

In API Explorar, I sent a JSON array like [{},{}], but only the first records was updated.

Does import set support JSON array?

Regards,
Ming

[ARG645]

Please see the attached workaround. Use the below code in your Scripted Rest API. All you need here is your Import set table name. I tested this in my PDI and it worked well. 

var requestBody = request.body;
var requestData = requestBody.data;
var importSetTable = 'u_your_importset_table_name'; //@ARG: this is all you need to know
var customImportSetLoader = new ImportSetLoader(importSetTable,requestData);
customImportSetLoader.startImport();

Below is the Script Include for Custom Import Set Loader. Create a copy of this in your instance.

var ImportSetLoader = Class.create();
ImportSetLoader.prototype = {
    initialize: function(importSetTable, requestBody) {
        this.importSetTable = importSetTable;
        this.requestBody = requestBody;
        this.importSetRowCounter = 0;
        this.importSetSysID = this.createImportSet();
    },
    startImport: function() {
        for (var jsonRow in this.requestBody) {
            this.createImportSetRows(this.requestBody[jsonRow]);
        }
		this.closeImportSet();
    },
    createImportSet: function() {
        var importSertGr = new GlideRecord('sys_import_set');
        importSertGr.initialize();
        importSertGr.setValue('mode', 'synchronous'); //@ARG: to make sure transform map runs immediately after importset rows are inserted
        importSertGr.setValue('state', 'loading');
        importSertGr.setValue('table_name', this.importSetTable);
        importSertGr.setValue('short_description', 'Bypassing importsetAPI: Synchronous REST transformation');
        importSertGr.setValue('creation_source', 'ws_insert');
        importSertGr.max_row_id = 1; //@ARG: Not sure what this is
        var importSetSysID = importSertGr.insert();
        return importSetSysID;
    },
	closeImportSet: function(){
		var importSertGr = new GlideRecord('sys_import_set');
		importSertGr.get(this.importSetSysID);
		importSertGr.setValue('state', 'loaded');
		importSertGr.update();
	},
    createImportSetRows: function(jsonRow) {

        var importSetRowGr = new GlideRecord(this.importSetTable);
        importSetRowGr.initialize();
        importSetRowGr.setValue('sys_import_set', this.importSetSysID);
        importSetRowGr.setValue('sys_import_row', this.importSetRowCounter);
        importSetRowGr.setValue('sys_import_state', 'pending');
        /*@ARG: Map all the columns in the Request Body to the Import set Columns  */
        for (var jsonColumn in jsonRow) {
            try {
                importSetRowGr[jsonColumn] = jsonRow[jsonColumn];
            } catch (e) {
                gs.log('Unable to import the column ' + jsonColumn + ' Error:' + e.getMessage());
            }
        }

        importSetRowGr.insert();
        this.importSetRowCounter++;//@ARG: Incriment the import set row Counter, this is used by the transform map to sequentially process the import set rows
    },
    type: 'ImportSetLoader'
};

This solution will be optimized and improved, but for now, what i provided can be used as a workaround. I see many questions in the community related to the limitations of WebService Import set. Once I have this research completed, I will update this thread with the upcoming article. The Article will explain the detailed process of inserting multiple records into the Import set from scripted rest api. 

Please mark the answer correct/helpful if applicable. 

Thank you,

Aman Gurram

Integrations & Authentications Practitioner

[Kristina2]

Hey Aman , 

This was very helpful . Do you have an updated version of this article . 

Couple of questions 

• I do not see , where we paarsing the multiple records from JSON and inserting into import set Row . 
• Can we do the mapping in the transform maps ., why do we have to do it with request body . ( Referring to line "

   /*@ARG: Map all the columns in the Request Body to the Import set Columns  */

Thank You 

@Aman Reddy Gurram 

[Ankur Bawiskar]

Hi,

As rightly suggested you can't use Import Set API to import more as one record in one REST call.

sharing link for reference:

https://community.servicenow.com/community?id=community_question&sys_id=1ad86c5cdbe2fb405129a851ca96...

You can go with Scripted REST API approach which would give you the capability to define your own request and response format

You can parse the json array and insert record into import set table

Once record is inserted into import set table then transformation will trigger automatically

Regards
Ankur

[amila]

With the quebec we have added REST Insert Multiple to address this issue.

https://developer.servicenow.com/dev.do#!/reference/api/quebec/rest/c_ImportSetAPI

[Hetang Modi]

ServiceNow allows you to send an array of JSON in the Import Set API. You can refer this documentation for the same - https://developer.servicenow.com/dev.do#!/reference/api/utah/rest/c_ImportSetAPI#import-POST-insertM....