Four Stars

How to update tmap after source/target column changes

I have a simple job that moves data from a database table to salesforce via tmap.  I modified the source table to add a new column, how do i get tmap to recognize and present the new column?  when i retrieve the table changes, it asks to "propagate" the changes, but the newly added column is never added to my tmap.  is there a way to refresh the tmap component such that it will see the new columns?  this is really frustrating as the only way i have found to work around this is to create a new job and remap everything.  there must be an easy way to do this.  any help would be greatly appreciated.  thanks!!

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Ten Stars

Re: How to update tmap after source/target column changes

You need to sort your missing Jar issue. Just click on the Jar icon and search your machine for that Jar. If you cannot find a version of it, then just download it.

However you do not need to do it this way. The reason you are getting nothing is because nothing is being sent from your database component. You might have the column in your SQL but there is no column in the schema for it. In your DB component, go to the "Component" tab and click on the ellipsis button next to "Edit Schema". This will bring up the schema that you DB component is using. You need to add your new column there. It should then ask if you want to propagate your schema (although since you've already created it in you tMap, it may not). Every component (well, most) has a schema that must be set. The SQL query and the schema are not the same thing here.

Rilhia Solutions
14 REPLIES
Ten Stars

Re: How to update tmap after source/target column changes

Can you post a screenshot of your tMap (the mapping screen) and let us know what is missing. I suspect that this will be very easy to help you resolve, but we need to know precisely what is going on first. 

It might also be good to see the schema of the component that is passing data to the tMap.


Also, do you have the table schema saved in the repository. Are you attempting to use this?

Rilhia Solutions
Nine Stars TRF
Nine Stars

Re: How to update tmap after source/target column changes

Hi,

If you change something before the tMap and decide to propagate changes, only the input part of the tMap (the left side) will be change (new column added). For the output part (the right side), you need to add it by yourself and map it manually with corresponding input field. Then answer yes to propagate question. If the next component is a tSalforceOutputXxx, the change will be propated automatically.

TRF
Four Stars

Re: How to update tmap after source/target column changes

I attached 2 pictures, the first is of the source.  here is the sequence of events:

  1. create metadata to db table (account_load)
  2. create metadata to salesforce (account)
  3. create job to move data from db to salesforce
  4. add new column to db_table (account_load)

Now i want to use that new column in my job.  when i go to the job, the column is not in the source query, so i added it.  when i go to the tmap component, i want to now map that new column, but its not available.  see first arrow in the pic.  i have tried at the bottom of the tmap source, to add the new column - second arrow.  i can add a column here and map it, but when i run job, the data in the new column is not being used, meaning it is always null/blank in the target/salesforce.  

 

To get around this for now, i create a brand new job and remap everything, this works fine.  but the mapping is tedious and i assume that this is a basic operation for all etl developers.  Again, i dont know talend very well, but I would hope that there was a way to "refresh" the jobs after metadata has changed to include those changes.  appreciate your help.

 

Thanks!!

 

talend_source.jpgtalend_tmap.jpg

Four Stars

Re: How to update tmap after source/target column changes

Note first pic is the source, arrow points to the new column that i added to the query. if i hit guess query it doesnt find the new column. the 2nd pic is the tmap which shows the missing attribute.
Ten Stars

Re: How to update tmap after source/target column changes

OK, the issue here is because your tMSSqlInput component is using a BuiltIn schema. This in itself isn't bad, but if you change your DB schema you MUST manually update it when you change the SQL (or click the "Guess Schema" button).

Rilhia Solutions
Four Stars

Re: How to update tmap after source/target column changes

not sure i understand, if i change it to repository, it wants me to install sql jdbc driver.  but the metadata has the new columns, so why cant the input module see these?  

Ten Stars

Re: How to update tmap after source/target column changes

You don't need to set it to use a repository schema (although that would be better), all you need to do is set your query correctly (as you have done) and then click the "Guess Schema" button. You said you were clicking the "Guess Query" button. The "Guess Schema" button derives the schema from the SQL you have used. If that doesn't work then you can just set your columns by configuring the schema yourself. 

Rilhia Solutions
Four Stars

Re: How to update tmap after source/target column changes

when i "guess schema" it prompts me to install a jdbc driver for sql server.  this has to be available already as i am reading/writing to/from sql server.

Four Stars

Re: How to update tmap after source/target column changes

talend_jdbc.png

Four Stars

Re: How to update tmap after source/target column changes

And the thing that is really the issue for me, is the tmap component. i add columns to the metadata, add columns to the query, but they dont show in tmap. and if i manually add the column to tmap, then use it, i get no data in that column. thanks again!
Ten Stars

Re: How to update tmap after source/target column changes

You need to sort your missing Jar issue. Just click on the Jar icon and search your machine for that Jar. If you cannot find a version of it, then just download it.

However you do not need to do it this way. The reason you are getting nothing is because nothing is being sent from your database component. You might have the column in your SQL but there is no column in the schema for it. In your DB component, go to the "Component" tab and click on the ellipsis button next to "Edit Schema". This will bring up the schema that you DB component is using. You need to add your new column there. It should then ask if you want to propagate your schema (although since you've already created it in you tMap, it may not). Every component (well, most) has a schema that must be set. The SQL query and the schema are not the same thing here.

Rilhia Solutions
Four Stars

Re: How to update tmap after source/target column changes

eureka! so i edited the schema as part of the source and now the new column is available to the tmap. this is exactly what i wanted to do. thanks! now the perfect solution is when i hit propagate, that it would propagate to the componenets and i would not have to do this manually. or even in the schema edit, there was a button to refresh. i had to type the new column in by hand. anyway, this seems to be the solution, not perfect but at least i dont have to recreate the job.
Ten Stars

Re: How to update tmap after source/target column changes

If you sort out the missing driver as I described, that will sort your issue with updating the schema from the query.  

Rilhia Solutions
Five Stars

Re: How to update tmap after source/target column changes

What is the approach for tELTmap components ?

 

in my job, i am using tELTVertica map, and i see the new columns in my input schema definition  but its not reflecting in my TELTmap components.  how to propagate the new columns in TELTMap component .