JDBC File Loader (via MID Server)

[enojardi]

How many times have you thought? I wish I could use JDBC to connect and extract data from a flat file instead of a database.

Well… This application (first release / beta version) does exactly that… and more:

* Connect using JDBC to a file via the MID Server

* Support for any flat file (CSV, TXT, etc)

* Support for zipped (.zip) files (containing a flat file)

* Choose any column separator you like

* Choose header row number or no header row at all

* Support for wildcard (*) and dynamic (scripted) file names

* Support for processing multiple files at once (using * wildcard)

* Load all rows from the file or execute your own "SELECT" SQL statement

* Select Import Set mode: Load only or Load & Transform (if a map is available)

* Bonus (experimental): onComplete file action: Copy, Move, Rename or Delete the file!

Link to App in Share: ServiceNow Share

[stevemac]

Hi,

revisiting the issue of ECC records (queue = output, name = JDBCFileLoaderProbe, state = processing) being reprocessed when a MID server agent is restarted (or communications reestablished between the instance and the agent).  This can cause data to be loaded with an invalid import set, import sets being deleted and / or JDBC File loads failing

this issue relates to two business rules:

• JDBCFileLoaderSensor (GUID = e393d9d96f0c35008a9810bd5d3ee4fc, when = Before)
• ECC Queue - mark outputs processed (OoTB, GUID = 27185a880a0a0b0200fb09569b9de87d, when = After)

JDBCFileLoaderSensor (& the function JDBCFileLoader.runSensor) will set the state of the JDBCFileLoaderProbe input record to processed if an error is not detected.  As this business rule runs before the OoTB rule ECC Queue - mark outputs processed, the condition to execute ECC Queue - mark outputs processed is not met.  As a result the state of the JDBCFileLoaderProbe output record does not get updated from processing to processed

Jonny - wondering if you can recommend a fix and incorporate it into the scoped application version you're working on.  The following options come to mind

• updating the business rule JDBCFileLoaderSensor so that it also updates the state of the related output queue entry (ie current.response_to) if the current.name = JDBCFileLoaderProbe
• new business rule triggered when a MID Server agent status changes to down.  When this occurs update ECC output queue entries (name = JDBCFileLoaderProbe, state = processing) to processed.  (this is how we were planning on handling it until will understood the relationship between the two business rules)

thanks,

Steve

JDBCFileLoaderSensor

(current.name == "JDBCFileLoaderProbe" || current.name == "JDBCFileLoaderProbeResult" ) && current.queue == "input" && current.state == "ready"

(function(){
			
	var sensorResult = JDBCFileLoader.runSensor(current.name, current.source, current.payload);
	
	if(sensorResult == ""){
		current.state = "processed";
	} else {
		current.error_string = sensorResult;
		current.state = "error";
	}
	
	current.processed = gs.nowDateTime();
	
})();

ECC Queue - mark outputs processed

current.agent.startsWith("mid.server.") && current.queue == "input" && current.state != "processed" && !current.topic.startsWith("queue.") && current.topic !="config.file"

setProbeProcessed(current.getValue('response_to'));

function setProbeProcessed(sys_id) {
    if (!sys_id)
        return;
    var gr = new GlideRecord('ecc_queue');
    if (!gr.get(sys_id))
        return;
    gr.state = 'processed';
    gr.update();
}

[stevemac]

Currently testing an update to the business rule JDBCFileLoaderSensor. 

So far so good. 

• JDBC File Loader continues to work
• ecc_queue entries for JBDCFileLoaderProbe are getting set to 'processed' once the corresponding input record has been received
• a restart of the MID Server Agent does not trigger re-transmission of the file

(function(){
			
	var sensorResult = JDBCFileLoader.runSensor(current.name, current.source, current.payload);
	var grECC = new GlideRecord('ecc_queue');
	var encQuery = '';
	
	if(sensorResult == ""){
		current.state = "processed";
	} else {
		current.error_string = sensorResult;
		current.state = "error";
	}
	
	current.processed = gs.nowDateTime();
	
		
	//this business rule is an onBefore and prevents the ECC business rule setting the original output entry for JDBCFileLoaderProbe to processed
	//we need to do this to prevent the output queue entry being reprocessed upon MID server restart / reconnection
	//Need to consider if state should be set to same as input record (ie 'processed' or 'error')
	if (current.name == 'JDBCFileLoaderProbe') {
		encQuery = 'sys_id=' + current.response_to.toString();
		encQuery += '^state=processing'; //only want to update if output record is processing (not sure what else might change this)
		grECC.addEncodedQuery(encQuery);
		grECC.query();
		//gs.log('JDBCFileLoaderProbe: encoded query is ' + grECC.getEncodedQuery());
		while (grECC.next()) {
			//gs.log('JDBCFileLoaderProbe: updated record is ' + grECC.getUniqueValue());			
			grECC.state = 'processed';
			grECC.update();
		}
	}
	
})();

I don't have much experience with the ECC queue.  Hoping for some advice on the correct state of the output record in the event the input record indicates an error.  Would it be better to set the output record state to 'error' or is 'processed' OK?

thanks,

Steve

[stevemac]

Ended up updating the JDBCFileLoaderSensor business rule with the following.  Since its update a month ago we have had no further occurrences of JDBC transfers being reinitiated nor of orphaned rows 

(function(){
			
	var sensorResult = JDBCFileLoader.runSensor(current.name, current.source, current.payload);
	var grECC = new GlideRecord('ecc_queue');
	var encQuery = '';
	
	if(sensorResult == ""){
		current.state = "processed";
	} else {
		current.error_string = sensorResult;
		current.state = "error";
	}
	
	current.processed = gs.nowDateTime();
	
		
	//this business rule is an onBefore and prevents the ECC business rule setting the original output entry for JDBCFileLoaderProbe to processed
	//we need to do this to prevent the output queue entry being reprocessed upon MID server restart / reconnection
	if (current.name == 'JDBCFileLoaderProbe') {
		encQuery = 'sys_id=' + current.response_to.toString();
		encQuery += '^state=processing'; //only want to update if output record is processing (not sure what else might change this)
		grECC.addEncodedQuery(encQuery);
		grECC.query();
		//gs.log('JDBCFileLoaderProbe: encoded query is ' + grECC.getEncodedQuery());
		//gs.log('JDBCFileLoaderProbe: row count' + grECC.getRowCount());
		while (grECC.next()) {
			//gs.log('JDBCFileLoaderProbe: updated record is ' + grECC.getUniqueValue());			
			grECC.state = 'processed';
			grECC.update();
		}
	}
	
})();

[palanivelc]

Hi ,

we have a requirement to import a large file from mid server path ,I have tried this JDBC file loader utility and it loads the 100 thousand rows in 28 minutes, is there any modification to complete it by less than 3 minutes? , since we need to import 2.5 million rows with in an hour.

Thanks

Palanivel.C

[stevemac]

Some options that come to mind

• Split the file into multiple smaller files (say 100K rows), add additional MID server agents and create new JDBC jobs (one for each file)
• In the JDBC job set the ECC Payload size to Extreme.  This will reduce the number of chunks being transmitted from the MID Server agent to the instance
• With regards to the data be transmitted - are there any columns with no data?  If there are - get these eliminated from the file
• Any option to import the file earlier and transform it at the required time?
• What options exist to reduce the row count (eg - if it is a full file - are you able to generate a delta file outside of ServiceNow and only transmit that)

I'm not convinced you'll get the throughput increases you're after.  You may need to look at alternative import methods (SOAP / REST).  Have a look at the ServiceNow doco (REST).  I have never benchmarked these so have no idea on how much you can load (& then transform) per hour