IntegrationHub ETL for inbound integrations - ImportSET API + JSON Column

[KN]

First of all this isn't a question but rather an answer which hopefully  provides some assistance for someone else struggling on how to trigger IH-ETL for inbound integrations (POSTing to Import Set w/ importsetAPI).

1. Create a data source with a type of Custom (Load by Script) and check that Data in single column is true.

2. Add a JSON-column in the script  like so  (the second parameter is the length of the field): "import_set_table.addJSONColumn('u_data',64000);" . Remember to "load all records" so the import set table is created.

3. Create your ETL (only provide basic details, 1st step).

4. Create an entry  REST Insert Multiples in sys_rest_insert_multiple where you'll select your source table (your newly created import set table) and make sure you'll select the correct Transformation type (atleast for /now/import/{stagingTableName}/insertMultiple it was asynchronous) and add your column mapping entry. 

5. POST to your import set (example of the JSON body that should be used for insertMultiple) : 

{
   "records":[
      {
         "<Column Label 1>":"<value>",
         "<Column Label 2>":"<value>"
      },
      {
         "<Column Label 1>":"<value>",
         "<Column Label 2>":"<value>"
      }
   ]
}

 

6. After a successful POST you should be able to retrieve your newly created import set in ETL as a sample and the attributes from your JSON should be automatically extracted as mappable key-value pairs in ETL in a similar manner as it works with a setup where we're pulling CMDB data via GET methods.

JSON-columns add a dynamic layer for CMDB integrations because we don't have to worry about e.g length of our fields in import sets anymore and updating new attributes doesn't require much effort, intergration pushes a new field to the JSON columns and you can just open up ETL and retrieve the newest sample import set and the new field is there for you to map it.

I haven't had the time yet to investigate how to get the same setup working without insertMultiple but it's something that i'll try to check and update on the same post.

docs especially look for the paragraph "POST /now/import/{stagingTableName}/insertMultiple"

https://developer.servicenow.com/dev.do#!/reference/api/zurich/rest/c_ImportSetAPI#import-GET?navFil...