Wanting to Import data using external REST service on a schedule using Import Set / Transform Map

james_whitt · ‎09-13-2012

We're running into an issue where right now we are using Scheduled Jobs to kick off a script include that uses a MID Server to connect to a 3rd party system for importing data into a ServiceNow table directly. What we want to do is use Import Sets and Transform maps also, so that we aren't pulling for 3rd part software using REST and entering data directly into the tables using the script.

With that said, we are stuck on figuring out how exactly how to "Load Data" into the import set and then kicking off then "Run Transform". We know the transform map needs to be asynchronous and that if we were using odbc, file, xml, we would go the Import Sources route, but we can't. Have to be ServiceNow scheduled import using MID server to consume the REST services.

Any help would be appreciated.

tantony · ‎09-30-2016

When you open an import set entry, the upper table shows the transform history and lower table shows the import set rows.You will notice that for a web service import set by not specifying a hard coded sys_import_set, Transform History table(sys_import_set_run) will have as many entries as the import set rows. Which makes me think that each import set row is run separately. If you specify a import set sys_id, there will be only one row in the Transform History table(sys_import_set_run) table.

karthik73 · ‎10-19-2016

davidself3, were you able to change the status of the import set from 'loading' to 'processed' once the load from web service is complete?

davidself3 · ‎11-28-2016

Karthik, No, after a certain time period, the import set flips to "processed". I haven't paid close attention to the time, but I believe it's a day or two. In our instance it's not important to know when the import is complete, only that the locations are current with our corporate location API.

davidself3 · ‎01-06-2017

Karthik, This is above my scripting ability, haven't done it before. But you could potentially add script in the xml loop that says when it reaches 0 go ahead and set the status of the sys_import_set record to "processed". I may end up having to do this if my team wants me to button it up. If that's the case, I'll certainly update this thread with my solution. Hope that helps!

davidself3 · ‎11-28-2016

Also, I ended up having to flip this whole integration over to XML because json kept timing out. Here's the code base utilizing the XMLhelper script include.

try {

   //Send REST get to grab XML blob

   var r = new sn_ws.RESTMessageV2('Location API', 'get');

   var response = r.execute();

   var responseBody = response.getBody();

   var httpStatus = response.getStatusCode();

   var parsed = new XMLHelper().toObject(responseBody);

}catch(ex) {

   var message = ex.getMessage();

}

//Create a new import set

var crImpSet = new GlideRecord('sys_import_set');

crImpSet.initialize();

crImpSet.mode = 'synchronous';

//crImpSet.mode = 'asynchronous'; //switch to this for testing import set will not trigger transform map

crImpSet.table_name = 'u_location_api_import'; //Set the extended importset table

crImpSet.state = 'loading';

crImpSet.insert();

var locs = parsed.Location;

var restGR = new GlideRecord('u_location_api_import'); //Query extended import set rows table

//Loop through the xml chunks until 0 remain creating import set rows to be transformed

for (var i = 0; i < locs.length; i++) {

   restGR.initialize();

   restGR.u_id = locs[i].ID;

   restGR.u_alternateidentifier = locs[i].AlternateIdentifier.ID;

   restGR.u_name = locs[i].Name;

   restGR.u_typedescription = locs[i].TypeDescription;

   restGR.u_latitude = locs[i].Address.Latitude;

   restGR.u_subdivision = locs[i].Address.Subdivision;

   restGR.u_typecode = locs[i].TypeCode;

   restGR.u_countryname = locs[i].Address.CountryName;

   restGR.u_addressline1 = locs[i].Address.AddressLine1;

   restGR.u_postalcode = locs[i].Address.PostalCode;

   restGR.u_formattedaddress = locs[i].Address.FormattedAddress;

   restGR.u_city = locs[i].Address.City;

   restGR.u_longitude = locs[i].Address.Longitude;

   restGR.u_phonenumber = locs[i].TelephoneNumber.PhoneNumber;

   restGR.u_county = locs[i].Address.County;

   restGR.u_timezonecode = locs[i].TimeZone.TimeZoneCode;

   restGR.u_address = locs[i].Address.FormattedAddress;

   restGR.u_opendate = locs[i].LocationMilestones.OpenDate;

   restGR.u_company_code = locs[i].CompanyCodeDescriptor.ID;

   restGR.u_subtypecode = locs[i].SubTypeCode;

   restGR.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above

   restGR.insert();

}

Hope this helps folks building REST API's in ServiceNow!