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

[james_whitt]

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]

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);
   //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;
   if (locs[i].AlternateIdentifier) {
   restGR.u_opendate = locs[i].AlternateIdentifier.ID;
   }
   restGR.u_name = locs[i].Name;
   restGR.u_typedescription = locs[i].TypeDescription;
   if (locs[i].Address) {
   restGR.u_latitude = locs[i].Address.Latitude;
   }
   if (locs[i].Address) {
   restGR.u_subdivision = locs[i].Address.Subdivision;
   }
   restGR.u_typecode = locs[i].TypeCode;
   if (locs[i].Address) {
   restGR.u_countryname = locs[i].Address.CountryName;
   }
   if (locs[i].Address) {
   restGR.u_addressline1 = locs[i].Address.AddressLine1;
   }
   if (locs[i].Address) {
   restGR.u_postalcode = locs[i].Address.PostalCode;
   }
   if (locs[i].Address) {
   restGR.u_formattedaddress = locs[i].Address.FormattedAddress;
   }
   if (locs[i].Address) {
   restGR.u_city = locs[i].Address.City;
   }
   if (locs[i].Address) {
   restGR.u_longitude = locs[i].Address.Longitude;
   }
   if (locs[i].TelephoneNumber) {
   restGR.u_phonenumber = locs[i].TelephoneNumber.PhoneNumber;
   }
   restGR.u_county = locs[i].Address.County;
   if (locs[i].TimeZone) {
   restGR.u_timezonecode = locs[i].TimeZone.TimeZoneCode;
   }
   restGR.u_address = locs[i].Address.FormattedAddress;
   if (locs[i].LocationMilestones) {
   restGR.u_opendate = locs[i].LocationMilestones.OpenDate;
   }
   if (locs[i].CompanyCodeDescriptor) {
   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();
   }
}catch(ex) {
   var message = ex.getMessage();
}

I've made some updates because we discovered that the post to the import set table was failing if the dot walked values in the array were empty. Figured I'd share this to help others.

Best,

David

[Nitya Mudaliar]

Hi All,

I am a similar requirement - schedule import of data via REST call. I created a transform map and coalesce on a string field. When I run this transform manually using load data -   it works perfectly.   But when I run it using a script like the one above - it inserts all and coalesce does not work.

I am doing something wrong?

Thanks,

Nitya

[Dinesh Kumar11]

Hello david,

I Am Facing Some Prolem As Same Issue.... I Created One Data Souce  And for  That I Created Schedule Data Import ....It Is getting propery now no problem, in this proccess i want to schedule automatically transform map to this,

where you your code in ....

it is getting like this please help me after this

thanks 

dinesh

[Jeff316]

I'm stuck trying to import from an external API a list of data centers which I will import into an import set then transform into a custom Data Center table in ServiceNow.

I cannot figure out the parsing in the scheduled script.

In the example above I don't know where Locations.Location comes from.

var locs = parsed.Locations.Location;

[Raghu Loganatha]

This is really helpful. Thanks for sharing.