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 Jonny,

Thx for taking the time to respond

Answers to your questions

• Current instance version = Geneva Patch 6 (we should be starting work on upgrading shortly)
• JDBC version - I'll need to check, but suspect it is not the current version in the share.   To the best of my knowledge it hasn't been changed since you developed it for us
• Not sure how long this has been occurring for.   Our support team has changed.   Believe the issue caused by the MID Server restarts has been observed whilst on Geneva Patch 6.   There is an issue in that version causing the MID Server agent to go offline.   As such were are restarting MID Server agents more frequently & at times when existing transfers are yet to complete
• I'll test JDBCFileLoaderProbe are set to complete and report back

shembop - thanks for your update on how you handled this

regards,

Steve

[stevemac]

We are running JDBCFileLoader v1.2 with the following updates to the script include JDBCFileLoader.

- line 306 of v1.2 changed from gs.eventQueue("jdbc_file_loader.load_error", sourceRec, error); to gs.eventQueue("jdbc_file_loader.error", sourceRec, error);   This was done to properly trap an error & from that generate a notification

- added the following to the execute part of the script include

this.fileSource.u_last_run_results = "";

this.fileSource.u_last_run_datetime = gs.nowDateTime()

Testing a JDBC datasource

- ECC outbound queue entry stays as processing.   The completion of the JDBC job (Last Run Status = OK) does not alter the ECCoutbound record's status

- Restarting the MID Server causes the file to be transferred again

- Restarting the MID Server after the ECC outbound queue entry is set to "processed" (changed via the UI) does not cause the file to be transferred again

So - I'll ask our support team to install the latest version (2.1) into non-Prod & I'll review the changes and retest

Wondering - is there a mechanism to change the ECC outbound queue record from "processing" to "processed".   I do not know how the interaction between the MID Server Probe and the ECC inbound records (payload) handles this - or if the JDBCFileLoader script include needs to cater for it

regards,

Steve

[enojardi]

stevemacI would highly recommend updating to 2.1 - You missed on 1.3, 1.4, 2.0 and 2.1 updates... But let's wait to hear from shembop on what version he's on.

ECC entries are agnostic of the Data Source or the job being executed in the app, since this is all leveraging the native functionality of how the MID Server works.

So let me understand more, if the MID Server is restarted, the "whole" file is loaded again?

Lastly, one workaround way to change "manually" the ECC queue to 'processed' would be by a business rule that triggers when the MID Server is down? Would that work?

Jonny.

[stevemac]

Jonny - I highly agree with you too.   I have just dropped an email to our support team to this effect

Regarding the behaviour

If there is a ECC outbound record in "processing" state (name = JDBCFileLoaderProbe) and the MID Server is restarted then

• If the file is still present then the entire file is transferred again (ie new ECC inbound records (name = JDBCFileLoaderProbeResult) with file in the payload) & the original ECC outbound record (name = JDBCFileLoaderProbe) stays in a state of "processing".
• if the file is no longer present then a new inbound ECC record is generated (Name = JDBCFileLoaderProbe) with a state of "error" and the original outbound record (name = JDBCFileLoaderProbe) has its state set to "processed"

As to your workaround - I like the idea.   Will test & report back

thx,

Steve

[stevemac]

enojardi - initial testing of a business rule has been successful.   Will be doing further testing next week.

Business Rule is on ecc_agent with condition that Status changes to Down

script

[code]

(function executeRule(current, previous /*null when async*/) {

      //This code will look at the ecc_queue for all JDBCFileLoaderProbe outbound entries

      //and set them from processing to processed

      //this is to prevent the transfers initiating again if the mid server restarts

      var eccAgentName = 'mid.server.' + current.name.toString();

      var gr_ecc = new GlideRecord('ecc_queue');

      var encodedQuery = '';

      var recordsProcessed = 0;

      var logMessage = '';

      var logMessageCommon = 'MID Server Agent ' + current.name.toString() + ' is down.';

      encodedQuery += 'name=JDBCFileLoaderProbe';

      encodedQuery += '^queue=output';

      encodedQuery += '^state=processing';

      encodedQuery += '^agent=' + eccAgentName;

      gr_ecc.addEncodedQuery(encodedQuery);

      gr_ecc.query();

      while (gr_ecc.next()) {

              logMessage = logMessageCommon;

              logMessage += ' Setting JDBCFileLoaderProbe entry to processed for ';

              logMessage += gr_ecc.getUniqueValue().toString();

              logMessage += ', Source=' + gr_ecc.source.toString();

              logMessage += ', Created ' + gr_ecc.sys_created_on.getDisplayValue();

              gs.log(logMessage);

              recordsProcessed ++;

              //Set the ecc_queue record to 'processed'

              gr_ecc.state = 'processed';

              gr_ecc.update();

      }

      gs.log(logMessageCommon + ' Total ecc_queue messages set to processed was ' + recordsProcessed);

})(current, previous);

[/code]