Transform map onStart script to check if trailer exists in csv file, if not abort the transaction.

Lavanya6 · ‎05-25-2020

I am running Transform map to insert and update user records. Data source is a csv zipped file. My logic is not working when i am checking for trailer in the source.

Requirement is:

1.If the source field does not have trailer information (as in the attachment) the transaction should get aborted that is the transform map should not happen.

2.If the record count from source does not match the record count in the transform map table the transaction should be aborted.

Ankur Bawiskar · ‎05-29-2020

Hi Lavanya,

here is the script; I tried in onStart transform script but somehow it didn't work out.

It worked well when added in onBefore transform script

script below

Note: Ensure you give proper

1) import set table name; I have used u_testing_data_load_csv

2) correct import set field name; -> u_recordcount and u_h

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	var trailerCharacter = '';

	var recordCountField;
	var gr1 = new GlideRecord('u_testing_data_load_csv');
	gr1.orderByDesc('sys_import_row');
	gr1.addQuery('sys_import_set', source.sys_import_set);
	gr1.query();
	if(gr1.next()){
		recordCountField = gr1.u_recordcount;
		trailerCharacter = gr1.u_h;
	}

	if(trailerCharacter != 'T'){
		log.error('Missing trailer row as the character T cannot be found in column H');
		error = true;
	}
	else{
		// found the tralier row now check whether the count of rows is same as record count column

		var gr = new GlideRecord('sys_import_set_row');
		gr.addQuery('sys_import_set', source.sys_import_set);
		gr.query();
		var totalRowsIncludingTrailer = gr.getRowCount();

		var actualDataRows = totalRowsIncludingTrailer - 1;

		if(actualDataRows != recordCountField){
			log.info('Total rows are not matching hence setting error as true and it would halt entire transformation');
			ignore = true;
			error = true;
		}
		else{
			// allow transform to run
		}
	}

})(source, map, log, target);

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Kieran Anson · ‎05-26-2020

Hi,

You can use the below onStart transform script

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
    var table = new GlideRecord('sys_import_set_row');
    table.addQuery('sys_import_set', source.sys_import_set);
    table.addQuery('u_h', 'T');
    table.query();
    if (table.next()) {
        var tablecount = new GlideRecord('sys_import_set_row');
        tablecount.addQuery('sys_import_set', source.sys_import_set);
		tablecount.query();
		var count = tablecount.getRowCount();
			if(!(table.getValue('u_recordcount') == count)){
				//abort if trailer does not match record count.
				ignore = true;
			}
    } else {
        //no trailer row found in importset, aborting entire transformation/
        ignore = true;
    }
})(source, map, log, target);

If my reply helped with your issue please mark helpful 👍 and correct if your issue is now resolved. ✅
By doing so you help other community members find resolved questions which may relate to an issue they're having.

Lavanya6 · ‎05-26-2020

Hi Kieran,

I tried with the above script as is. In logs it gives me that 'Transform ignored by onStart script'.

I also tried logging to check if it goes into the if loop, it is always executing else.

Data coming from source has trailer row and the count is match.

Thanks!

Kieran Anson · ‎05-26-2020

Possible your fields don't match to what i'm imported. add a log statement to get the row count

var table = new GlideRecord('sys_import_set_row');
    table.addQuery('sys_import_set', source.sys_import_set);
    table.addQuery('u_h', 'T');
    table.query();
log.info("import set ID is " + ource.sys_import_set);
log.info("Row count is " + table.getRowCount());

Lavanya6 · ‎05-26-2020

Thankyou Kieran,

You were correct. When tried logging, I am not getting row count it gives row count as 0.
Looks like getRowCount() doesnt work.

Any other suggestions.

Thanks!