How to check that an import is successfully loaded in a onStart or onComplete event of a transformation ?

[Rodolphe]

Hi,

When daily importing data from an external database, if records are not present in the source database anymore, we want to mark the corresponding records in the target table as retired. To do so, we implemented an onComplete script on the transform map that retires all records that were not updated during the transformation.

We realized that, if the import fails for any reason (the source database is down, password was changed, etc), the transformation will be executed: the onStart scripts and onComplete scripts of the transformation will be executed, and the transformation will appear as completed in the transform histories. As result, our onComplete script marks ALL the records as retired because the import set is empty (due to the load failure). The exepected behavior is: our script may not be executed when the data load fails.

How can we check in our onComplete script that the import set was successfully loaded ?
Or how can we configure ServiceNow to not execute the transformation when the import fails ?

How to reproduce:

1) Create a valid data source that executes a query on an external database (for exemple: MySQL)
2) Create a valid transform map with a onComplete event that logs the message "onComplete event started."
3) Create a Schedule import for this datasource and transform map, and click "Execute Now"
4) Modify the 'data source' so that the import fails. For example, set a bad password.
5) In the Schedule import, click "Execute Now' again.
6) Check the results in the "Transform histories". Both transformations will have the status "Completed" and both transforms will contain the log "onComplete event started."

Thank you.

[Andrew Westerv4]

A simple solution is to check if your import set that ran actually had a row count of records. If it did, then you know the import was successful and you can run your retirement process. Just modify your existing onComplete script for this validation and you should be fine. If you want to get even fancier, you could add an else clause to create an incident to your team to check the data source.

var impSet = new GlideRecord(import_set.table_name);
impSet.addQuery('sys_import_set', import_set.sys_id);
impSet.query();

// If we actually imported in data, we can process retirement
if (impSet.getRowCount() > 0) {
// Retirement script goes here
}

[Brian B]

Old thread, but i just wanted to say this worked perfectly for my use case.   If we were unable to connect to the onPrem SQL DB, or more specifically, if the table in SQL did not have any records brought in for import...  I did not want to run the cleanup of old relationships that are maintained in the cmdb_rel_ci table that hadn't been updated recently.  This worked great, without modification (except adding our script include inside the 'if' statement along with a log.info and log.warn depending on the result.

Thanks!