Issue with Importing data in to the staging table

[Siva Kedari Vee]

Hi All,

I'm trying to import data from JSON file in to the import set table using a fix script from the staging table i need to transform the data in to the target table.

By running the below script in a fix script populates the staging table with the required information from the JSON file but the problem here is that it is not transforming the entire results (for example if the JSON has 50 results it is populating only 8 results remaining are missing) can someone help me why the other records gets skipped.

I'm getting results.length in the script is same as the number of results in the JSON file but when i'm iterating it through a loop it is not populating completely. Any help would be appreciated.

var dsId = '13dc9d224778r1101b92b37c346d436d';

try {
	var attachment = new GlideSysAttachment();
	var attachGr = attachment.getAttachments('sys_data_source', dsId);
	if (attachGr.hasNext()) {
		attachGr.next();
		
		var contents = attachment.getContent(attachGr);
		var jsonContents = JSON.parse(contents);
		gs.info('Data Import: Got JSON with ' + jsonContents.count + ' results');

		var results = jsonContents.results;
		gs.info('Data Import: results length is ' + results.length);
		
		if (results != null && results.length > 0) {

			var impSetGr = new GlideRecord('sys_import_set');
			impSetGr.initialize();
			impSetGr.setValue('mode', 'synchronous');
			impSetGr.setValue('table_name', 'x_weoi2_cloud_db_imp_db');
			impSetGr.setValue('state', 'loading');
			impSetGr.insert();


			for (var i = 0; i < results.length; i++) {
				
				gs.info('Data Import: Importing Record ' + i);

				var importGr = new GlideRecord('x_weoi2_cloud_db_imp_db');
				importGr.setValue('sys_import_set', impSetGr.getValue('sys_id'));
				importGr.setValue('account_id', results[i].accountId);
				importGr.setValue('address', results[i].configuration.endpoint.address);
				
				importGr.setValue('db_name', results[i].configuration.dBName);
				importGr.setValue('engine', results[i].configuration.engine);
				importGr.setValue('engine_version', results[i].configuration.engineVersion);
				
				

				var tags = results[i].tags;
				for (var j = 0; j < tags.length; j++) {
					if (tags[j].tag.startsWith('company')) {
						importGr.setValue('company', tagValue(tags[j].tag));
					}
					
					if (tags[j].tag.startsWith('environment')) {
						importGr.setValue('environment', tagValue(tags[j].tag));
					}			
								
									
				}

				importGr.insert();
			}

			impSetGr.state = 'loaded';
			impSetGr.update();


		} else {
			gs.info('Data Import: No results in file');
		}

	} else {
		gs.info('Data Import: No attachment found');
	}
} catch (ex) {
	gs.error('Data Import: ' + ex.message);
}

	function tagValue(tag) {
		if (tag != null && tag.length > 0) {
			return tag.substring(tag.indexOf('=') + 1);
		} else {
			return '';
		}
	}

[Siva Kedari Vee]

@Ankur Bawiskar 

Any help with this

[Ankur Bawiskar]

Hi,

so did you check the json is iterating correctly and inserting correct number

if correct number of rows are created in import set; each one will get transformed

Regards
Ankur

[Siva Kedari Vee]

Hi @Ankur Bawiskar 

The problem here is that the contents in the JSON doesn't match with the records in the staging table. I ran the above fix script to populate the records but i'm getting few records from the JSON only (there is some issue with the iteration)

[Ankur Bawiskar]

if the JSON is array of json objects and it has 100 objects within that then it should iterate 100 times

seems your json is not in correct structure