Search code examples
informaticainformatica-powercenter

Informatica: How to load two sources of different schema into same target (flat file)?

Here is the scenario:

I have a source which contains invoice line details:

INV#    DATE    QTY     PRICE
123     12/16   1       50
123     12/16   2       20

I have another pipeline where I have aggregated the details to form the header

INV#    DATE    TOTALPRICE  PURCHASE#   LOC     TAX
123     12/16   70          456         1000    7

I want to sort and load both these records into the same tab-separated flat file using Informatica

Thus the flat-file output would be:

123     12/16   70      456     1000    7   
123     12/16   1       50
123     12/16   2       20

Any suggestions?

The only way I can think of is concatenating all the header and detail columns in one string and adding a union transformation to create one file.

Solution

  • Follow the below steps to have this done:

    1) You need to create a new port(header_detail_ind) of type integer for the header and detail pipeline. For this you can use two expression transformations one for each pipeline. The header records should have a value of 1 for this port and the detail records should have a value of 2.

    2) Next use a union transformation with both the pipelines as input. Use the expression transformations in the above step to make the output of both the pipeline(no of ports and datatype) same so that it can be mapped to the union transformation. You can add some empty fields in both the pipelines to achieve this.

    3) Next add a sorter transformation and sort the data by #INV and the new port header_detail_ind. By this way you will have the header and the detail records in the order you expect.

    4) Now you can use a expression transformation to prepare the output as required by the target definition. This will have one output port for each of the port required by the target definition. You can have different logics for the header and detail records which can be differentiated by the port header_detail_ind.