Target Table Name in Source Column

[jerome_rajan]

We are faced with a slightly tricky situation here. We have source data coming in a single table. This table say 'SOURCE' has a column called 'TGT_TBL_NM' that defines the name of the target table that the corresponding row is inserted into. A sample of our source looks like

Code: Select all

col1  col2 col3 TGT_TBL_NM
1       2     3      Tab A
2       3     4      Tab A
4       5     6      Tab B

The first 2 rows would have to be inserted into Tab A and the last row into Tab B. We've got roughly 25-30 such targets.

I remember a similar question being asked at an interview but never paid much heed. Any pointers to solving the problem?

[ray.wurlod]

For a small finite number of known table names multiple output links from a Filter or Transformer stage would serve as a solution - one link going to each of the known table names.

A dynamic solution would be more difficult. Perhaps use a server container (or server job) with a Folder stage to create separate files per table, then a sequence to read the table name from each file and set a job parameter containing the table name in the job that populates the table from the file.

You might be able to use the XML stage rather than a Folder stage.

[jerome_rajan]

Thanks Ray.

We're looking for a dynamic solution. I lost you at

...a sequence to read the table name from each file and set a job parameter containing the table name...

Can you please elaborate?
I'd be very interested to know how the XML Stage can help since that would mean a purely parallel job.

[SURA]

I am not sure about the solution what i am trying to give.

You need a Master Sequence for LOOPING (Get the list of tables names and loop it)

Then you need two DS Job. 1st one can be a Multiple instance job like below

Code: Select all

src  lkup with src (only tbl name) and write into a dataset

in here lkup where clause & dataset file name will get the tbl name as an input param

Final job can be a RCP load job.