informaticainformatica-powercenterinformatica-cloud

How to create Target Files Dynamically in Informatica

How to create Target Files Dynamically, If Deptno=10 then create Target file as DEPT10.txt, If Deptno=20 then create Target file as DEPT20.txt, If Deptno=30 then create Target file as DEPT30.txt

Solution

  • You can achieve this by following below steps in Informatica.

    1. Add a column in target 'Add FileName column to this table' called out_file_name.
    2. User sorter to Order by Dept ID.
    3. Then Use an expression transformation. In the expression transformation create the below ports and assign expressions. Here v_* are variable ports, o_ are output ports.
    v_curr_dept_id= dept_id
v_flag  = IIF(v_curr_dept_id=v_prev_dept_id,0,1)
v_prev_dept_id  = dept_id
o_flag = v_flag
o_file_name = dept_id||'.txt'
    1. Use a transaction control to create different files now. IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
    2. Link o_file_name to column out_file_name in step1. Link other columns accordingly.
      Whole mapping should look like this -
    SQ.... SRT > EXP > TXN >TGT