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
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!
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.
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..
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