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.

[ChrisBurks]

Wasn't sure if this was answered yet but the following is a list of steps to import data from an Outbound REST call in combination with a MID Server using a Scheduled Job and hold it with in an   Import Set table using Asynchronous Import Set and Transform Map. Then schedule a job to perform the transform.

1. Create a table that extends Import Set Row
   1. Give it a name
   2. Create a new application menu or Place module under Existing application menu or none
   3. Uncheck the auto create user role if not desired
   4. Create necessary columns
2. Create Transform Map
   1. Enter name of above table with .list appended in the text filter box
      1. <table_name>.list
   2. Click on Transform Maps
   3. Click New
   4. Give it a name
   5. Assign the target table
3. Create an import set
   1. Enter sys_import_set.do in text filter box
   2. Set mode to Asynchronous
   3. Keep State at Loading
   4. Set table to import set table created in step one
   5. Give description if needed
4. Create Outbound REST call
   1. When creating the call method be sure to populate the MID Server field with desired MID Server
5. Create a Scheduled Job (not a Scheduled Import) to populate the import set
   1. Click New
   2. Choose to run a script
   3. Set the desired times when to execute script
   4. Grab the scripted code from the REST call by clicking the Preview Script Usage
   5. Create a script that will use the REST call script usage to populate the table created in step one. You can also do any manipulation in here before populating or use the script sections in the transform map.
      1. Example:
         try {
           var r = new sn_ws.RESTMessageV2('name_of_your_outbound_rest_message', 'get');
           r.setStringParameter('a_parameter', 'parameter_value');
           var response = r.execute();
           var responseBody = response.getBody();
           var httpStatus = response.getStatusCode();
         }
         catch(ex) {
           var message = ex.getMessage();
         }
         var results = responseBody.split(','); //This depends on what's in your response
         var restGR = new GlideRecord('u_table_from_step_one');//Important
         restGR.initialize();
         restGR.u_assigned_to = result[0]; //this would depend on your response
         restGR.u_assignment_group = result[1]; //this would depend on your response
         restGR.u_short_description = result[2]; //this would depend on your response
         restGR.u_description = result[3]; //this would depend on your response
         restGR.sys_import_set = 'sys_id of the import set created in step 3'; //Important
         restGR.insert();
6. Modify the Scheduled Job named "Asynchronous Import Set Transformer" to meet your requirements to run all Asynchronous import sets or just the one specific Rest Call import set if you haven't done your specific modifications in step 5 or in the Transform Map

I hope that is useful and what you're looking to do.

[tantony]

Chris,I see you are manually creating an import set here.Will it be retained in the system?.My understanding was that for each import a new one is created and it will be deleted after use in a few days.Is it done specifically to make it asynchronous?I am using standard import table(imp_location) and for me I don't see a need for the transformation to be asynchronous.So do I need the sys_import_set assignment? Why would somebody need asynchronous transformation?

[ChrisBurks]

Hi Tessy,

The answers to your questions are as follows.

Q. Will it be retained in the system?

A. Yes, it should. It is the same setup that follows when creating an Inbound Web Service. Import Sets and Import set rows are deleted per a Scheduled cleanup which can be modified by excluding the table you're using. Click the "Cleanup" module under "Import Set Tables"

Q. Is it done specifically to make it asynchronous?

A. No, not specifically. I did this just to separate it out from the normal Inbound Web Services. But you can use one of the OOB tables created or create a new Inbound Web Services to use.

Q. So do I need the sys_import_set assignment?

A. Yes because that is what tells what import set to add the Import Set Row to. Remember the very first table created is an Import Set Row not the import set itself. So it needs to know what import set to belong.

Q. Why would somebody need asynchronous transformation?

A. I used asynchronous because it was mentioned as a criteria from the originator of this post. They wanted control over the data before inserting it. Usually when using the inbound Web Services, although the holding table is populated first, the data gets inserted into the destination table right when the web services populates the holding table. I interpreted the post as they did not want the insertion to happen right away but to have it hold until some sort of verification or manipulation of data could be performed. Then have it insert into the destination table.

[tantony]

Thank you so much for your very clear answers for all of my questions. I have questions about using a MID server for a REST call.I intend to use the scheduled script execution job with two REST messages. The first call(to a Django/python application hosted on a linux server) will process the feed files, compare against application's( mirror of servicenow location table) Mysql database and put the create/update/delete decisions to a staging table, calculate the change percentage.If change is less than 10%, proceeds to the next REST message which will get the create/update/delete records from python application's staging table and updates import table(OOB imp_location table).Since the change is validated before importing and also since I want it to be completely automated, I can go for synchronous transformation to target table.

-In my situation what is the advantage of making the REST call through a MID server

-.Where will be the MID server hosted?

-Can I use any existing MID server for my purpose or do I have to   create a new one ?

[staceyrocheleau]

For the REST messages I perform, I don't specify the mid server and I supply the basic authentication by checking "Use basic auth" and supply the user id and password. I set up my REST messages in the REST Message section and then call this message from my script. In my case, I'm using the Airwatch API to pull data.

// Use Airwatch API to retrieve list of devices

  var r = new RESTMessage('Airwatch Devices', 'get');

  var response = r.execute();

I did try to use other methods to connect but found the basic auth was the only one that worked for me--the certificate wasn't working as expected. I would think you should be able to use your existing Mid Server for the REST call as long as everything is set up (i.e. if it needs a certificate rather than basic auth)...