This is a topic for which I haven't found practical, step-by-step instructions from SN docs, so here it goes:
Have you ever wondered, is it possible to bring in (CMDB) data in Import Set table and then consume it using IntegrationHub ETL Robust Transform, just they way you might been used to do using Transform Maps? It's easier than what you might have thought it would be. The idea is to have multi-column Import Set table, whose rows are handler and transformed by IH-ETL setup. This is how it's done:
1) Prepare a proper JSON file
Prepare a proper JSON file containing all the needed columns that will be integrated. All you need is one single object containing the target columns in key/value pairs.
Example JSON file contents:
{
"u_name":"Demo name",
"u_asset_tag":"demo_asset_tag",
"u_serial_number":"demo-serial_1",
"u_assigned_to":"beth.anglin",
"u_company":"ACME UK",
"u_short_description":"Demo desc",
"u_comments":"Demo comm",
"u_sys_class_name":"cmdb_ci_win_server"
}
2) Create a Data Source
Create a new Data Source as follows.
Field contents
- Name: <name for your Data Source>
- Import set table label: <label for you Import Set table>
- Import set table name: <this is autopopulated once the table label is given>
- Type: File
- Format: JSON
- Patch for each row: /
- Discard arrays: true (this is checked by default)
File attachment
Before saving the record, add the JSON file prepared above as attachment of the to-be-created Data Source record.
Save
Next, save the Data Source record.
Load All Records
Finally, use "Load All Records" Related Link located above the Related lists. This creates:
- Import Set table
- Import Set
- Import Set Row (containing the column values provided in the attachment JSON file)
3) Set Import Set table field specs
By default, the Import Set custom field columns lengths are set based on the contents of the JSON file (with minimum length of 40). What I prefer to do is to set the Import Set table's custom column lengths to match with the target table's corresponding columns' lengths. For example, if you intend to import CI descriptions to OOTB field Description (short_description), whose max length is 1000, you might want to set your Import Set table's corresdponing field's max length to 1000.
Another option is to use sys_property com.glide.loader.verify_target_field_size, whose default value is 'false'. Setting this property to 'true' makes your instance to automatically expand the field lengths of your Import Set tables' columns to match with the data imported in them.
More information on this:
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB1588592
4) Create and setup IntegrationHub ETL configuration
Finally, create and setup IntegrationHub ETL configuration to consume data from the Data Source / Import Set table created and defined above. In the first "Specify Basic Details" section, simply select the Data Source you created earlier in the "Data Source" field - and the Sample Import Set should get selected automatically. Then just setup and finish your ETL configuration - and you're ready to rock and roll!
Once the ETL conf is done, you can test your setup by bringing in data to your Import Set table e.g. using REST API Explorer / Import Set API. Import Set rows created in the Import Set table are automatically processed by IH-ETL using IRE.
If needed, you can simply add new columns to the Import Set table. The IH-ETL's UI isn't the most 'classy' one and getting new Import Set table's fields visible in ETL's UI might take some time + require some page refreshes. Also, remember to check which Sample Import Set you're looking at and make sure it has the needed data.
