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]

To reply to earlier questions. We are running version 2.0

I got around the issue until a fix is in place by creating a job that runs after my imports in the morning. Here is my code.

var djdbc = new GlideRecord("ecc_queue");

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

djdbc.query();

djdbc.deleteMultiple();

[adarsh-homepage]

Hello Jonatan,

We running in with a problem. JDBC data source is trying to run twice. Anything you can suggest on this. Also, can you let me know where to look for

version. Screenshot is from ECC log.

[stevemac]

Hi,

Between the jdbc_file_loader.load_complete (05:01) and the jdbc_file_loader.load_error (05:43) entries did the MID Server agent this job runs on go down or restart?

I have seen this behaviour in our environment.   You can also look at the ECC queue records (Name is JDBCFileLoaderProbe, Source is the sys_id of the JDBC Data Source, Queue is output).   You should see 1 outbound record initiating the transfer (it will have an updated timestamp matching the 2nd execution and two input records for the initial successful completion and subsequent failure)

If you see this after a MID Server restart - we are planning on temporarily addressing this via a business rule on ecc_agent.   We will trap a MID Server going down.   When it does - for all JDBCFileLoaderProbe records associated with that MID Server agent that are output and in a processing state - the state will be updated to processed.

regards,

Steve

[adarsh-homepage]

Hello Steve,

I checked the mid server logs and mid sever is Up and running successfully. The strange this is we have multiple JDBC data sources and it seems to be happening with randomly. Today the other one exclusive of the one mentioned above failed. It is the kind of cycle, it starts the probe for input and output is processed in a minute or two. But I assume continues to run and after a certain minutes it triggers the input probe which in turn troughs an error. So the job gets processed but than it through's   an error and no transform history is generated. The bigger concern is that we have set-up a BR to create an INC is any of these job fails. So I have multiple tickets now and little caught on what shall I look for.

Also, can we set-up these jobs to run only weekdays. Currently I dont see that option. Also, help me to locate the version of it.

[stevemac]

Hi

As to the why it is occurring - I'm unsure what's going on in your instance.   The only times we've seen the transfer re-initiated is when the MID Server agent goes down and then comes back up (according to the instance) and when we observed significant network delays between the instance and the MID Servers (I suspect the MID server agents went offline to the instance - but this is not confirmed).

As to scheduling week days - you can achieve this by scheduling it to run daily and adding a condition to check the day of the week.   I haven't got an example of this handy (we trigger the JDBC job externally to the instance (via Control M)).   We did something similar for conditional generation for some users' reports.   If I can find the script I'll share it with you.   From memory the script created a new GlideDateTime object with the correct timezone.   We then checked if the day was Mon - Fri and returned true if it was

good luck,

Steve