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

[Lavanya6]

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.

[Lavanya6]

Hi Ankur,

a) Yes, since it has T it should pass the check.

b) the column 'record count' gives you total records and in the trailer row it has the total record count. so yes record count + 1.

Thanks,
Lavanya

[Ankur Bawiskar]

Hi Lavanya,

Thanks for the info.

Will look into this tomorrow and let you know

Regards
Ankur

[Ankur Bawiskar · Accepted solution]

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

[Lavanya6]

Hi Ankur,

Thankyou for your help. It doesn't work for me with onStart script.

onBefore does work very well.

I do have other set of activities which are performed with onBefore script so i wanted to do it with onStart. However, I will go with this approach now.

Thanks again!

[Ankur Bawiskar]

Glad that it helped

I believe it doesn't work with onStart probably because it doesn't have access to source object although it is mentioned in docs

Regards
Ankur