topic Same job with multiple databases as output in Talend Studio

Same job with multiple databases as output

Anonymous — Tue, 25 Nov 2014 10:16:59 GMT

Hi there,
I couldn't find a solution for my issue anywhere.
I have a lot of jobs, some of them contain multiple mappings. The input is a SQL Server table or view every time.
Initially an Oracle table was set as output. It was working fine. But now I need to get the jobs working on SQL Server too.
I've created a new context variable which can determine whether MSSQL (0) or Oracle (1) is used. There are 2 filters after each mapping with this variable. This is my current solution:

This job fails if I don't have any of those systems installed, because it wants to create the connection to both databases. The 2 output databases are identical, and I don't really want to create the same job again for the same database on SQL Server.
Is there a possibility to enable only 1 connection at a time?
If not then is there any other way to distinguish between the outputs?
Thanks and regards,
J

Re: Same job with multiple databases as output

Anonymous — Tue, 25 Nov 2014 14:41:56 GMT

Hi, giramon..
i cannot imagine your ETL jobs so i assume you are using the context as a filter to determine which table in which database the data should be inserted/updated..
why don't you use the final output (the data that you want to insert/update) as an input of tMap (you need to add tMap)..
in that tMap, create 2 outputs (eg - mssql_out & oracle_out) and then use these outputs and point it to respective DB.. in your case, tMssqlOutput and tOracleOutput.. (it's easier if you have the DB connections (Oracle & MSSQL) in you metadata repository, just drag/drop and choose t<DBtype>Output then set up the table name, schema, etc..)
please refer attachment to understand what i mean..
hope that'll helps!

Re: Same job with multiple databases as output

Anonymous — Tue, 25 Nov 2014 15:00:19 GMT

Hi sky_angel,
I don't know why my attachment cannot be seen.
Let's hope it'll work now.

So this was my job. As you can see it's similar to your suggestion, but in this way only 1 mapping is created. And this is how I need it.
I don't want to create more jobs or more mappings, because they are huge sometimes and maintaining them in the future would be twice as painful.

Re: Same job with multiple databases as output

Anonymous — Tue, 25 Nov 2014 15:51:05 GMT

hi ,
if you're using a tis (entreprise ) talend version wrap it in a joblet. (modification in one place)
otherwise no real way to switch between 2 differents output within one job ...
regards
laurent

Re: Same job with multiple databases as output

Anonymous — Tue, 25 Nov 2014 17:24:51 GMT

Hi, giramon..
i wonder why you have to use tReplicate + 2 tFilterRow components if you only have 0 and 1 as your variables..
do you have variables other than 0 and 1? if no, then you just need to use 1 tFilterRow with its 2 outputs.. (Filter & Reject)
right click on tFilterRow > Row > Filter/Reject..
please refer the attachment..

Re: Same job with multiple databases as output

Anonymous — Wed, 26 Nov 2014 09:19:39 GMT

Hi sky_angel,
This simplifies my solution, so instead of a replicate and 2 filters I use just 1 filter, but my problem still exists.
Is there a possibility to disable the connection to an output without deactivating it in the job?
Or is it right what kzone said? Is it not possible to switch between 2 different type of databases?
Thanks and regards

Re: Same job with multiple databases as output

Anonymous — Wed, 26 Nov 2014 10:27:52 GMT

I guess that even with a JDBC component , you canot switch on different DB due to the fact that you cannot dynamically assign jar (driver).
regards
laurent