Trigger JDBC Data Source from UI Action and Scheduled Job

[mballinger]

Hello,

We currently have a JDBC Data Source and transform map set up to retrieve PO related data. Our problem is some POs are missing the PO Line items. I think this is probably because po line items are being added as the transform is occurring. 

There are 2 things I am trying to figure out:

1. Create a UI action that grabs single or multiple records from the PO table (based off of a filter with certain conditions), dynamically updates the SQL statement in the Data Source, and fetches all the PO Line items. Finally the transform needs to be ran after all records are loaded
2. Create a scheduled job that runs once daily that grabs POs (based off of a filter with certain conditions) that run the data source and transform all the loaded records.

Thanks!

[Ankur Bawiskar]

Hi,

You need to follow these steps

1) Create Scheduled Data Import and link your data source and keep it as Active=false

2) Then Trigger this scheduled data import from script like this

Same script can be used in UI Action which is server side and From Scheduled job as well

var exportGr = new GlideRecord("scheduled_import_set");
	exportGr.addQuery("data_source.name", "Data Source Name")
	exportGr.query();
	if (exportGr.next()) {
		gs.executeNow(exportGr); // this is for scoped app
		// SncTriggerSynchronizer.executeNow(schImp_GR); // this is for non scoped app
	}

Regards
Ankur

[mballinger]

@Ankur Bawiskar - How do I dynamically update the SQL statement? The data source sql statement needs to fetch records of PO's that have a status of "Ordered". I need to grab all PO #'s from ServiceNow after the filter is ran and update the SQL statement with the PO #'s the filter returned

Query:

status=ordered

Also, does the solution you provide run the transform as well?

[Ankur Bawiskar]

Hi,

Just before triggering the scheduled data import update the SQL query for that data source

Regards
Ankur

[mballinger]

When you say update the SQL query are you referring to manually? I want the query updated through a script. So I would do this in the script you provided me for the Scheduled Data Import?

Will the scheduled data import also run the transform? I've never created one before.