Need to pull data using SuccessFactors spoke (oData API) to Servicenow Staging tables

[Gaurav Gupta3]

Hi Everyone!!

Using SuccessFactors Spoke (oData API), tried to pull data from Successfactors to Servicenow staging tables. I was successful to pull data for three tables. However, all my efforts are failing for Locations table. Copied the OOB flow designer and want result only for 3 fields as of now, External ID, name and country.

Somehow after making some code as commented, I was able to pull the information for External id and Name, however country details are not populating at all.

[Harish Bainsla]

1. Set Up an External Data Source:

In ServiceNow, you need to configure an external data source to connect to the SuccessFactors oData API.

1. Navigate to "System Web Services" -> "External Data Sources."
2. Create a new record for the SuccessFactors oData API connection.
3. Configure the endpoint URL, authentication methods, and other necessary details.

2. Create Staging Tables:

You need staging tables in ServiceNow where you'll temporarily store the data before transforming and loading it into ServiceNow's main tables.

1. Define the structure of the staging tables to match the data you'll be pulling from SuccessFactors.

3. Create a Transform Map:

A transform map defines how data from the external data source (SuccessFactors) is mapped to fields in ServiceNow.

1. Navigate to "Import Sets" -> "Transform Maps."
2. Create a new transform map and specify the source and target tables (staging tables and ServiceNow tables).
3. Define the mapping between source and target fields, including any data transformations or scripts required.

4. Schedule Imports:

You can schedule regular imports to pull data from SuccessFactors into your ServiceNow staging tables.

1. Navigate to "Scheduled Jobs" -> "Scheduled Script Executions."
2. Create a new scheduled job that runs a script to perform the import.
3. In the script, use the import API to trigger the import process using the external data source, transform map, and staging table.

Here's an example script that you might use in the scheduled job:

var dataSource = new GlideExternalDataSource('<data_source_name>');
var importSetTable = new GlideImportSet();
var importSet = importSetTable.createNew('u_import_set_name');
importSet.setDataSource(dataSource);
importSet.load();
var importSetRun = new GlideImportSetRun();
importSetRun.setImportSet(importSet);
importSetRun.run();
if (importSetRun.getStatistics().getRecordCount() == 0) {
gs.addErrorMessage('Import from SuccessFactors failed.');
}

if you get answer please mark helpful and accept solution

[Gaurav Gupta3]

Thank you for the detailed response, however I am looking here for OOTB solution with respect to Successfactor spoke.  This spoke is installed using the plugin and staging tables already get created, all that is already working fine.  However, pulling data is an issue. 
I know my question is missing some important content yesterday so I have added more information today.
Please go through and suggest.

[Mohith Devatte]

Hello @Gaurav Gupta3 ,

Are you using OOB spoke and OOB flow designer ? 

And where are you stuck exactly ?

[Gaurav Gupta3]

Yes, I am using OOB Succcessfactor Spoke and six staging blank tables are already attached over there.  Out of which three tables are coming with relevant data.   However, the table "Locations" has been a challenge for me.   

Under Retrieve Locations Action, we can find the below OOB script

(function execute(inputs, outputs) {
var util = new SuccessFactorsUtil();
var fullPull = inputs.fullPull;
var date = util.convertDateToUTC(inputs.pull_modified_locations_from);
var filter = '';
var format = 'JSON';
// var expand = 'addressNavDEFLT/stateNav/picklistLabels';
// var resourcePath = 'FOLocation?$format=' + format + '&$expand=' + expand;
var resourcePath = 'FOLocation?$format=' + format;

if (!fullPull || fullPull == 'false') {
try {
if (typeof(date) == 'string' && date.trim() != '') {
filter = "lastModifiedDateTime ge datetimeoffset'" + date + "'";
resourcePath = resourcePath + '&$filter=' + filter;
} else {
throw new Error("When 'Retrieve All Locations' is false, 'Retrieve Locations Modified From' is mandatory");
}
} catch (e) {
throw new Error(e);
}
}

outputs.resource_path = resourcePath;
})(inputs, outputs);

I commented the two lines to shorten the url to obtain the results for required fields. 
Please check the output as under, External ID and name is coming up fine but issue is with country value.

I hope I am making sense to get the right results.  Please correct me if my approach is wrong.