How basically transform map will work

Tamil5 · ‎06-12-2017

Hi All,

I have an intermediate table in service now that will be act like a source table when LDAP schedule load runs.

So my source table will be 'imp_location' & the target table is 'cmn_location' (this configuration is done by someone)

When I check the cmn_location table records, everyday update is happening, but 'imp_location' table is empty all the time.

So I though, once the data transform completed these records will be deleted.

Now I need to import my own application data(it has location values) to 'cmn_location'. So I thought of using the same Transform Map.

Created a Datasource Source table. It automatically mapped the Transform Map here.

Created a Scheduled Import with this Data Source & executed.

But data did not move from 'imp_location' to 'cmn_location' table.

'imp_location' did not get cleared & cmn_location table did not get updated with the new values.

Can any one help me with this?

Thanks

Prici

Tamil5 · ‎07-04-2017

Hi Trena Fritsche,

Thanks for your time & really sorry for the delayed response.

Actually the solution was pretty simple.

I think I got confused a lot (since this the first time i'm working with Transform map).

My big mistake was, I tried to use the existing staging table which is populated by webservice. I thought, populating my data in that table will automatically moved to cmn_location.

And tried hard to make it work.

Then I tried to create a new staging table & mapped columns with cmn_location using transform map.

And executed my API code. It populated the data perfectly in my staging table & then automatically transferred to cmn_location.

My another issue,

I had some cleanup script in post transform script for staging table, so I was not sure whether it is transferring data properly.

Simply creating transform map the new staging table did the magic.

Thanks

Prici

View solution in original post

TrenaFritsche · ‎06-12-2017

Hi Prici,

As I'm reading through this string, I started to recall an issue that I have ran across using transform maps and fields that are reference fields, which I'm guessing the location field is from cmn_location. What I needed to do is write scripting to actually get the sys id of the location for each import row and make the target value a sys id instead of Text. Not sure if this is your problem without actually seeing it, but this is one issue that has stumped me in the past and sounds like it is happening in your case. Let me know if this might be the cause.

Thanks,

Trena

Tamil5 · ‎06-13-2017

Hi Ashish Nahar & Trena Fritsche

I think I got confused after reading all these.

Can you guys please tell,

when a scheduled load runs, whether the Source table will be auto cleared after the transform is completed.

If so, in my scenario I just need to manually clear the Source table. Rest of the part will be taken care by my scheduled jobs.

I think the scheduled job executed last night & updated few records.

Please advice.

Thanks

Prici

Ashish Nahar · ‎06-13-2017

This should help

check this article underlined.

Import Sets - ServiceNow Wiki

2.4 Import Set Tables

Cleanup: Graphical tool used for deleting old data or removing no longer needed import set tables. You cannot clean up import set tables that have a transform map defined in a different application scope, starting with the Fuji release.
Scheduled Cleanup: Schedule regular deletion of import set data older than a set number of days. Only data is removed, not the import table itself.

It is strongly recommended that you activate this job to ensure your import tables do not get extremely large. Deleting records older than seven days should be sufficient.

Import Set Tables: Expandable list of previous import set will contain references to the most recent import set records that have been uploaded from a data source.

TrenaFritsche · ‎06-14-2017

Hi Prici,

Did you get your transform map to work? Each Import has a "Set" number that will enable multiple loads of a file without the need to clear out the table, so I don't believe you were experiencing removed data. If the data was empty, there was probably a problem when the transform ran. Let me know if you are still having issues.

Thanks,

Trena

Tamil5 · ‎06-14-2017

Hi Trena Fritsche

Thank you so much for your help. Actually I'm struggling with this.

My requirement might be very simple, but I'm making it as complex.

I have the API code by which I can get the location values from my organization application.

Since we are referring the 'cmn_location' table for all location field I will have to insert/update my data to 'cmn_location' table.

In my instance I have seen a transform map(created by someone), they used 'imp_location' table as source & 'cmn_location' table as target (to update sys_user table in daily basis).

But 'imp_location' table has a WSDL url, by which some other external schedule job is updating 'cmn_location' table (am not aware of the external schedule call).

Here I have been asked to import my API result data to a staging table & then transform it to 'cmn_location'.

I have created a staging table with extends to 'Import Set' & created a Transform map.

If I refer this Transform map in a Datasource, I don't know what to select in Type/File retrieval method (since my data is available in a servicenow table)

Now if i click Test Load 20 Records, it is not working for me.

Import set not creating, its not loading data...

Hope I'm not confusing you..

Please help.

Thanks

Prici