etlinformaticainformatica-powercenterinformatica-cloudinformatica-data-integration-hub

how to compare column names and order of columns in two files in informatica cloud


The source files columns keep on changing every time, and so the data in the target table gets jumbled. I want to avoid this issue by creating some program in informatica cloud where it will check the column order and column name of the incoming source file with the existing(correct)source file. If the columns are matching, then only it should proceed with inserting the data to the target. Else it should fail the job. Thanks in advance


Solution

  • You can do it in two options.
    Option 1 - Use UNIX head -1 dailyfile > file1 and head -1 modelfile > file2. Then compare file1 and file2. if both are same, then its a good file else replace dailyfile with a 0 byte file and inform support team(or do whatever you want to).

    Option 2 - Little tricky.
    Create two pipelines - one for modelfile one for dailyfile.
    attach a exp transformation and add sequence gen(starts from 1 always).
    Then put a filter and pick up nextval =1.
    Then compare filtered result using joiner (you can join on all columns or concat them into a long string and compare on that column.)- if they are same, generate flag ='Pass' else 'NoPass'. The join SQ_dailyfile and this ouput - join condition will be on this flag column. If 'Pass'='Pass', it will produce rows else 0 rows. You can also call ABORT() when flag = 'NoPass' too.
    Whole mapping should look like below pic.
    Whole Map Diagram