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

[shembop]

Hello Jonatan,

Kudos on the loader. I have run into 2 issues, and I am hoping you can help me.

• I have had several times where I'm importing a job of say 80 lines. In the history, it says I got 80 lines successfully, but then I discover in the transform history that only 65 lines arrived. Do you know what would cause this behavior?
• This second one is a bit interesting. Apparently, the ECC jobs stay in the queue for a period of time. When the MidServer is restarted for patching, or other reasons, it seems that the old jobs are still there, and I end up with duplicate imports. Is there a setting where I can remove the job from the queue after completion? I know it is the job rather than the file because the records that come in are different than just the present file. (my import file is overwritten daily with new data)

[Daniel Pettet]

To point 1...

Check for a race condition in the event that is triggered "on complete". It's possible that the data has come into the system but the import set rows are still being saved to the DB and when the transform process starts on the import set it will then miss whatever rows are still trying to be saved to the DB properly.

Adding a sleep/scheduled delay of the same event can force the script to wait long enough that the DB should have finished writing all it's data and the import set can be transformed properly.

[stevemac]

hi Jonny,

We see the same behaviour as shembop - if a MID server is restarted, the transfer is initiated again

As a summary I have observed

- Initiating a JDBC job creates the import set and an entry in the ECC outbound queue (topic = javascriptProbe, Name = JDBCFileLoaderProbe, State = Ready)

- if the MID server is up then the state of the ECC outbound entry changes to "processing"

- the data is transferred from the MID server to the instance (ECC input queue records)

- import set rows are populated from the ECC payload into the table specified in the JDBC job (ie to an extension of sys_import_set_row)

- the ECC outbound queue entry stays in state of "processing"

- If the MID Server is restarted, for each ECC outbound queue entry (Topic = JavaScript Probe, Name = JDBCFileLoaderProbe, State = processing), then the transfer is attempted again.   Assuming a match to file(s) on the MID server

      * the records are transferred again

      * if the import set is not present, then the records are created in sys_import_set_row without a valid import set record (ie they are orphaned).   Orphaned records are currently not cleaned by the import set cleaner job - which is causing some issues

Hoping you can comment on the state of the ECC outbound queue record and how / why the transfer is initiated again event though the MID server agent already has the request

  - should the state change to processed once the file is transferred from the MID Server

  - how to prevent multiple transfers of the same data (ie MID server restart whilst the original transfer is still occurring)

thanks,

Steve

[shembop]

I can tell you what I did while waiting for a real answer. All of our transfers should happen between 5 & 6 in the morning, so I created a scheduled job to clean the queue at 6AM.

var djdbc = new GlideRecord("ecc_queue");

djdbc.addEncodedQuery("nameLIKEjdbc^state!=processed^ORstate=NULL");

djdbc.query();

djdbc.deleteMultiple();

[enojardi]

Hi shembop and stevemac,

I'll be honest with you guys, I know there's been some changes to the code of the MID Server in latest releases, so that could have something to do with it. I built this custom app in Eureka over 2 years ago, so inevitably, it was bound to have a few hiccups in time, hehe.

Here would be my follow up questions to understand the issue a bit more. I don't have a solution for it, haven't touched that app since late 2014, but I know it's used widely out there for a lot of customers. Also remember, that this is a "custom" application (i.e. not supported by ServiceNow)

• Are you using the latest version 2.1 from Share? I say this because someone from ServiceNow Professional Services added some enhancements a while ago, specifically to the Script that controls the MID Server Probe. Could that be it?
• What version is the instance, to the patch/hotfix level? Same for the MID Server?
• When did this start to happen? After an upgrade?
• What happens if the MID Server is restarted *after* all the JDBCFileLoaderProbe are set to 'Complete' for that specific run? Does it still re-run the same job and create ophans?

Fogg   giosos imamHave you seen this issue before while implementing the app?

Cheers,

Jonny.