Import sets stuck in 'Loading' state

[stevehuitt]

SH

We run a scheduled job that calls a web service via a REST message to get a list of user updates from our HR system. The job's script creates an import set with a state of loading and a mode of asynchronous. using a custom table that extends Import Set Row. The script processes the web service payload inserting update records to the import set table. We don't necessarily process all the user updates from the web services so it is possible that sometimes no records are inserted into the table used with the import set. Once all of the updates have been processed the state of the import set is changed to loaded and the script triggers a transformation using a previously defined transformation map. This works fine but occasionally the import sets do not change to the loaded state but remain in the loading state. What could cause this? Would this be a result of not having inserted any records into the table used for the import set?

Here's the relevant code. This is the code from a script include used to create the import set:

getImpSet: function(impTbl) {
      var impSet = new GlideRecord('sys_import_set');
      impSet.initialize();
      impSet.table_name = impTbl;
      impSet.mode = 'asynchronous';
      impSet.state = 'loading';
      impSet.insert();
      return impSet;
},

And it is called with the following:

var impTable = 'u_user_changes';
var impSet = utils.getImpSet(impTable);
var restGR = new GlideRecord(impTable);

The updates from the web service are inserted using the restGR object. Here is the code from the script include to perform the transform:

doTransform: function(impSet, mapName) {
      var map = new GlideRecord('sys_transform_map');
      map.addQuery('name', mapName);
      map.query();
      if (map.next()) {
              var thread = new GlideImportSetTransformerWorker(impSet.sys_id, map.sys_id);
              thread.setBackground(true);
              thread.start();
      }
},

And is called as follows:

impSet.state = 'loaded';
var mapName = 'User Changes';
utils.doTransform(impSet, mapName);

Note that we use the same approach, and script include functions, for updating other data from other web services,

Regards,

Steve Huitt

[stevehuitt · Accepted solution]

It turns out this is due to a failure in defensive programming. The web service payload has a property that contains a list of updates where each update is a JSON document. However, when there is a single update the same property is not a list of JSON documents but is a single JSON document. Since this was not documented in the web service doc the code processing the response does not account for this possibility. When the response was a single update the code fails with a reference to an undefined variable and bypasses changing the state of the import set to 'Loaded' leaving it in the 'Loading' state.

Steve Huitt

[Dan156]

stevehuitt ,

I have run into this same problem did you ever find a work around for this? Also, are there any adverse side-effects of this bug that you have noticed?

[stevehuitt]

danschroeder

I did change my code to better check the inbound web service payload and that has prevented the occurrence of updates sets stuck in the loading state. As it turned out in   our situation these update sets had zero records so no harm done. Until the root cause was known we implemented a scheduled job to change the state of the update sets stuck in loading to cancelled. Again, these had no records so no data was lost. We have not seen this issue when an update set contained data.

Regards, Steve

[VaranAwesomenow]

https://community.servicenow.com/community?id=community_blog&sys_id=a6fe65b41bfd1d18c790ece6b04bcbf0

It is observed that when a data source of type REST (IntegrationHub) returns an attachment with no records such as below

[]

then ServiceNow will leave the import sets in loading state.

In this case response via REST API is valid and returned a 200 response, but there are no records to provide in response to the GET request hence record details are empty.

In order to handle this in scheduled import post script run the following script, this will verify if there are any records in import set row for the import set that is just created by the scheduled import and there are no rows then it marks import set to canceled instead of leaving it in loading.

//reference : https://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/import-sets/reference/r_DataImportScriptingOptions.html
//https://developer.servicenow.com/blog.do?p=/post/training-glideagg/
//Check if import set is in loading state and number of records are 0 then set importset state to canceled.
var isetSysId = import_set.sys_id;
var isetState = import_set.state;
var enQuery = 'sys_import_set=' + isetSysId;
    var aggISetRow = new GlideAggregate('sys_import_set_row');
    aggISetRow.addAggregate('COUNT');
    aggISetRow.addEncodedQuery(enQuery);
    aggISetRow.query();
    // Process returned records
    while (aggISetRow.next()) {
        var recCount =""; 
        recCount = aggISetRow.getAggregate('COUNT');
        //gs.loggingstatement("isetSysId=" + isetSysId + " count = " +recCount + " query = " + enQuery,'av'); - changed method name if logging needed change it to gs.log
        //current.state = 'Pending transform';
        if(recCount==0 || JSUtil.nil(recCount)) {
            setImportSetToCanceled(import_set.sys_id);
        }
    }

function setImportSetToCanceled(iSetid) {
    var grISet = new GlideRecord('sys_import_set');
    grISet.get(iSetid);
    grISet.state = 'cancelled';
    grISet.setWorkflow(false);
    grISet.update();
}