How to create Target Files Dynamically in Informatica

Question

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

Answer 1

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'

4. Use a transaction control to create different files now. IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
5. 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