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

Tamil5 · ‎06-12-2017

Hi Ashish Nahar,

Sure, I will try with a new transform map & update

Thanks

Prici

Michael Fry1 · ‎06-12-2017

If you're going to use Type = File, you'll also need to selected a format.

The File Retrieval method, should be a server or attachment. If you have a spreadsheet that you want to import, just use Load Data.

Tamil5 · ‎06-12-2017

Hi Michael Fry

Thanks for pointing that, actually I have a query on that as well.

I'm just trying to populate the source table using my scheduled script. This transform map has to transform my data to Target table.

So what should I choose here in 'Type' (File/JDBC/LDAP).

Thanks

Prici

Ashish Nahar · ‎06-12-2017

Dear Prici, what is your Data source, when you say scheduled script, you are calling a web service using scheduled job or what exactly is being done. Could you please elaborate

Tamil5 · ‎06-12-2017

Hi Ashish Nahar

sure, I have a scheduled script which has API code to get location values from our organization application.

Am able to get the data & directly import to some other table.

But, now I need to insert the same values in to my 'cmn_location' table which already mapped in Transform map as Target table.

So I thought of using the same Transform map for my insert/update.

If you see in my screen shot, In my Data Source I just gave the 'Importset table name' as 'imp_location' and the Transforms related link section populated automatically.

FYI - I tried to create new Transform map.

But in my Data Source now am getting two Transforms, since both map's source table is 'imp_location'

Thanks

Prici