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 · Accepted solution]

Hi,

update the SQL query for the data source from script itself.

Yes once you run this scheduled data import it would trigger the data source, fetch the data and start the transformation as well.

var rec = new GlideRecord('sys_data_source');
rec.get('name', 'Data Source Name');

rec.query = 'Your Updated Query Here';
rec.update();

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]

I do have one final question. After the Data Import Set is created, does it automatically transform on its own or do I have to do something to transform the data? I have never worked with transforms and data imports before so am not sure especially when this is being scheduled

[Ankur Bawiskar]

@mballinger 

Once you run the above script the transform maps linked to that data source automatically runs.

[Greg Githens]

Is there a way to execute the data source and transform map separately?

For example if you wanted to load the data source, validate the records in the import set, and then run the transform map?