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.

davidself3 · ‎09-29-2016

Hey Chris,

First off, thanks for sharing this, it was incredibly helpful.

My question is: I've built everything out as you instructed and am successfully populating the extended import set rows with my REST get. But it's not automatically transforming when hitting the table. Am I missing something?

I have the import set in Mode = Asynchronous & state = Loading. I see the child import set rows in state = pending, but nothing happens (transform map is created and aligned to the u_import_set_row_location_api table.)

I'm considering just writing to the imp_location table if I can't get this to work.

I'll include my scheduled script to help others stuck on the json parsing (which was a pain in *badword*). This API converts the get to our cmn_location table.

try {

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

   r.setStringParameter('Accept', 'application/json');

   r.setStringParameter('key', 'private');

   var response = r.execute();

   var responseBody = response.getBody();

   var httpStatus = response.getStatusCode();

   gs.log("GET SERIOUS 1" + responseBody);

   var parser = new JSONParser();

   var parsed = parser.parse(responseBody);

   gs.log("Parsed JSON" + parsed);

   /*for (var n in parsed.Locations.Location[0]){ //use this to see your json objects

   gs.log("Parsed[" + n +"]= " + parsed.Locations.Location[0][n]);

   }*/

   gs.log(parsed);

}catch(ex) {

   var message = ex.getMessage();

   gs.log("DAVE Catch Contents " + message);

}

var locs = parsed.Locations.Location;

gs.log("Parsed Array Size locs = " + locs.length);

var restGR = new GlideRecord('u_import_set_row_location_api'); //Extended import set row table

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

restGR.initialize();

restGR.u_id = locs[i].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.sys_import_set = '0ce7417e13cee600c4513372e144b068'; //This is the import set in the extended table above

restGR.insert();

}

Sample of one REST json object returned from the GET for reference.

{

"Locations": {

"@count": 173,

"Location": [

{

"@locale": "en-US",

"ID": Private,

"Name": "Minneapolis Office",

"TypeCode": "Private",

"TypeDescription": "Hq (headquarters)",

"Address": {

"Latitude": Private,

"Longitude": -Private,

"AddressLine1": "Private",

"City": "Minneapolis",

"County": "Hennepin",

"Subdivision": "MN",

"PostalCode": "Private",

"CountryName": "United States",

"FormattedAddress": "Private"

},

"TimeZone": {

"TimeZoneCode": "CST",

"TimeZoneDescription": "Central Std Time",

"TimeZoneOffset": {

"OffsetCode": "UTC",

"OffsetHours": "-06"

}

},

"TelephoneNumber": {

"FunctionalTypeDescription": "Main",

"PhoneNumber": "Private"

},

"LocationMilestones": {

"OpenDate": "Private",

"LastRemodelDate": "null"

}

},

tantony · ‎09-29-2016

It is not tranforming automatically because it is an asynchronous import set. By default web service import sets are synchronous and transforms immediately.You need to create an asynchronous mode import set and provide its sys_id only if you want to do examine it , make sure it is correct and transform it manually( by clicking the reprocess link inside the import set) or let the system do it by nightly process.

If you want to process it immediately automatically , you can just comment out this line:

restGR.sys_import_set = '0ce7417e13cee600c4513372e144b068';

System will automatically create a synchronous import set for you each time and the transformation happens immediately

http://wiki.servicenow.com/index.php?title=Web_Service_Import_Sets#gsc.tab=0

davidself3 · ‎09-30-2016

Haha, it's always something simple. I'm good to go!

Thanks for the quick response, both you and Chris!

ChrisBurks · ‎09-29-2016

Hi David,

I guess since this discussion became a little long it may have lost it's intended result. Normally working with import sets you wouldn't have to go through the process I wrote out. That process was brought about because my understanding of the original post was that the user did not want their data processed immediately but the process to be kicked off on a schedule.

@ tantony - Thanks for the reply

davidself3 · ‎09-30-2016

Alright guys,

So here's my final product. I added the initialize to create a fresh new import set each time and load all the import set rows in it. Hopefully this will help others until ServiceNow gets us a better REST get web service.

To clarify, this is a scheduled script that will trigger daily and sync locations in the cmn_location table.

Thanks again for your help guys!

try {

   //Send REST get to grab json blob

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

   r.setStringParameter('Accept', 'application/json');

   r.setStringParameter('key', 'Private');

   var response = r.execute();

   var responseBody = response.getBody();

   var httpStatus = response.getStatusCode();

   gs.log("Response Body " + responseBody);

   var parser = new JSONParser();

   var parsed = parser.parse(responseBody);

   gs.log("Parsed JSON" + parsed);

   /*for (var n in parsed.Locations.Location[0]){ //use this to see your json objects

   gs.log("Parsed[" + n +"]= " + parsed.Locations.Location[0][n]);

}*/

gs.log(parsed);

}catch(ex) {

   var message = ex.getMessage();

}

//Create a new import set

var crImpSet = new GlideRecord('sys_import_set');

crImpSet.initialize();

crImpSet.mode = 'synchronous';

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

crImpSet.state = 'loading';

crImpSet.insert();

var locs = parsed.Locations.Location;

gs.log("Parsed Array Size locs = " + locs.length);

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

//Loop through the json 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_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.sys_import_set = crImpSet.sys_id; //This is the sys_id from import set above

   restGR.insert();

}

//At this point we populate the import set and its rows then the transform map does its thing automatically