Hi,
I have the following need. I have 200 plus source tables and respective target tables (one to one mapping). On daily basis, I need to look into source and update /insert the target. Here source is a custom database (which can be connected using ODBC) and target is MSSQL.
One way to achieve this creating 200 individual jobs for reading from the respective source table and load to specific target table.
Other way which I am thinking is write a single job and load all the tables from source and run that job 200 times for 200 different tables. For this options, source and target schema need to be dynamic. One table may contain 5 columns and other may contain 100 columns and those could be with different data types.
Can we do something like this using single Talend job? If so, any suggestions or past scenariop examples?
Thx,
Siva
Hi,
Thanks for sending the post. But, there are not much details in the post. I know how to do if it i just an insert by using dynamic schema of TIS. But, in the case of update, we need to pass key name to the update key. That key could be different from table to table. How can I get the key name on dynamic basis?
Thx,
Siva
Hi, Thanks for sending the post. But, there are not much details in the post. I know how to do if it i just an insert by using dynamic schema of TIS. But, in the case of update, we need to pass key name to the update key. That key could be different from table to table. How can I get the key name on dynamic basis? Thx, Siva
Hi Siva, Dynamic schema concept implementation is not possible in the TOS. Here is a possible workaround - Read your database tables and write data into csv files... give table specific file name to each file, use context variables for file names. You can use 200 queries in a flat file and iterate through it... - Repeat this job for all the tables - Generate 200 queries to load data from csv to database - Iterate through all the queries to insert records into the target database table. Try this approach.. Thanks Vaibhav
I can use TIS. I liked that apporach. But, Target tables can be inserted / updated. For updating, we need to set atleast one column as key. That key could be different from table to table. How can you set that key on dynamic basis?
Thanks,
Siva
Hi Siva,
Updates are not possible using this approach.
Can you think of making an intermediate temp table as staging and then fire a next set of queries to load data from intermediate table to your target table for update or insert.. this way you can set a key for update and insert as well...
But this is all going to be like a query based...
Another approach would be to use a Stored procedure which can give you desired results...
Vaibhav
Even if we have the intermediate table, we need to have numerous jobs for updating all the main tables right? update keys are still will be different for each and every update table right? Will that be possible by using dynamic queries in one job?
Hi Sabrina, I got badly stucked, is there any component which retrieves the info like, what are the tables used in the job? For example: I had three table input components in a job, those three components are joined with tmap, finally the results are kept in a table. on this process i need dynamically capture what all tables are effected.Is that possible in Big-data talend 6.0 Thanks, Blessing
