IntegrationHub ETL dynamically create entity field and mapping

[pethumdesilva]

Hello community members,

I have an ETL application and already created entity mapping using IntegrationHub ETL (3.2) guided experience.

But I want to customize the mapping, base on the user input(add new filed to entity and and map to target tables). this should be done via the script dynamically not using the IntegrationHub ETL (3.2 guided experience.

Is there any way to achieve this?

Best regards,

Pethum

[Kieran Anson]

Hi Pethum,

Can you provide a bit more context on "dynamically". You specify mappings in ETL in order to manipulate the data before assigning it to a field of a CI, you're unlikely to be able to achieve that dynamically as it requires human decision making. Unless I am misunderstanding your ask?

[pethumdesilva]

Hello Kieran,

Thanks for the reply, In this approach, custom field mappings are stored in a system property, allowing flexibility in adapting the ETL definition during scheduled imports. While a basic mapping is predefined within the ETL application, users can extend it by adding custom fields without modifying the core configuration. During the import process, the ETL reads the system property and dynamically incorporates these additional fields, ensuring a seamless and adaptable data transformation process. This method enhances configurability, enabling users to tailor data mappings based on evolving business needs while maintaining the integrity of the standard ETL framework.

Hope this is clear for you.

Best regards,

Pethum

[Kieran Anson]

There isn't a way to do this natively.

Some of the Service Graph Connectors would use script operations to do this, whereby they read a system property to know whether to map a value or not.

If you're building a solution that you're planning to put on the ServiceNow Store, you may want to speak with the store app review team for architectural advise. You may be over-complicating things with the new of simplifying the customers ability to adopt your ETL definition

[BurtCrepeault]

Hi Pethum,

I've done something similar with Integration hub and it is definitely possible. Here's an overview:

1. You need to understand the Robust Transformer data model in ServiceNow. The relevant tables are:
   1. cmdb_inst_application_feed (Robust Transformer) 
   2. cmdb_inst_entity (CMDB Integration Studio Entity)
   3. sys_rte_eb_entity_mapping (RTE Entity Mapping)
   4. sys_rte_eb_field (RTE Entity Field)
   5. sys_rte_eb_field_mapping (RTE Field Mapping)
2. You can insert new fields and field mappings, provided that you know the Transformer sys_id, and the relevant entity and their entity mapping sys_ids.
3. The code below shows two methods I use in a script include that can dynamically insert new fields and field mappings.

	/**
	 * Adds a single RTE entity field record to a Devices Transformer entity
	 *  {string} entitySysId - sys_id of the target cmdb_inst_entity
	 *  {string} fieldName - field name to register (e.g. 'serial_number')
	 * @returns {object|null} Object with sysId, field, and sourceSysId; or null if the insert failed
	 */
	addEntityField: function(entitySysId, fieldName) {
		var fieldGr = new GlideRecordSecure('sys_rte_eb_field');
		fieldGr.initialize();

		fieldGr.setValue('field', fieldName);
		fieldGr.setValue('name', fieldName);
		fieldGr.setValue('sys_rte_eb_definition', this.transformerSysId); // defined as a class property, but could also be passed as a parameter
		fieldGr.setValue('sys_rte_eb_entity', entitySysId);

		var fieldSysId = fieldGr.insert();
		if (fieldSysId) {
			return {
				sysId: fieldSysId,
				field: fieldName,
			};
		}
		return null;
	},

	/**
	 * Adds a single RTE field mapping record to a Devices Transformer entity mapping
	 *  {string} mappingSysId - sys_id of the target sys_rte_eb_entity_mapping
	 *  {object} sourceFieldSysId - sysId of the source field
	 *  {object} targetFieldSysId - sysId of the target field
	 * @returns {string|null} sys_id of the inserted sys_rte_eb_field_mapping record, or null if skipped or failed
	 */
	addEntityFieldMapping: function(entityMappingSysId, sourceFieldSysId, targetFieldSysId) {
		if (!fieldSysIdObj.sourceSysId || fieldSysIdObj.sourceSysId === '') {
			return null;
		}
		var fieldMappingGr = new GlideRecordSecure('sys_rte_eb_field_mapping');
		fieldMappingGr.initialize();

		fieldMappingGr.setValue('order', 100);
		fieldMappingGr.setValue('source_sys_rte_eb_field', sourceFieldSysId);
		fieldMappingGr.setValue('sys_rte_eb_definition', this.transformerSysId); // defined as a class property, but could also be passed as a parameter
		fieldMappingGr.setValue('sys_rte_eb_entity_mapping', entityMappingSysId);
		fieldMappingGr.setValue('target_sys_rte_eb_field', targetFieldSysId);

		return fieldMappingGr.insert() || null;
	},