Docstoc

How to Generate Dynamic Target Files with Informatica

Document Sample
How to Generate Dynamic Target Files with Informatica Powered By Docstoc
					How to Create Dynamic Files in Informatica

Scenario:
There is a source file with employee‟s data and “a target for each department if the count
of the employees in the department is more than 1”, has to be created and all the unique
department employees should be written to another Target File

Scenario Data:
Employee_Source.txt
101|Neena|Kochhar|NKOCHHAR@abccorp.com|AD_VP|17000|90
102|Lex|De Haan|LDEHAAN@abccorp.com|AD_VP|17000|90
105|David|Austin|DAUSTIN@abccorp.com|IT_PROG|4800|60
107|Diana|Lorentz|DLORENTZ@abccorp.com|IT_PROG|4200|60
111|Ismael|Sciarra|ISCIARRA@abccorp.com|FI_ACCOUNT|7700|100
113|Luis|Popp|LPOPP@abccorp.com|FI_ACCOUNT|6900|100
118|Guy|Himuro|GHIMURO@abccorp.com|PU_CLERK|2600|30
119|Karen|Colmenares|KCOLMENA@abccorp.com|PU_CLERK|2500|30
128|Steven|Markle|SMARKLE@abccorp.com|ST_CLERK|2200|50
132|TJ|Olson|TJOLSON@abccorp.com|ST_CLERK|2100|50
167|Amit|Banda|ABANDA@abccorp.com|SA_REP|6200|80
173|Sundita|Kumar|SKUMAR@abccorp.com|SA_REP|6100|80
178|Kimberely|Grant|KGRANT@abccorp.com|SA_REP|7000|20
200|Jennifer|Whalen|JWHALEN@abccorp.com|AD_ASST|4400|10
201|Michael|Hartstein|MHARTSTE@abccorp.com|MK_MAN|13000|20
202|Pat|Fay|PFAY@abccorp.com|MK_REP|6000|20
203|Susan|Mavris|SMAVRIS@abccorp.com|HR_REP|6500|40
204|Hermann|Baer|HBAER@abccorp.com|PR_REP|10000|70
205|Shelley|Higgins|SHIGGINS@abccorp.com|AC_MGR|12000|110
206|William|Gietz|WGIETZ@abccorp.com|AC_ACCOUNT|8300|110

Emp_Dept_Lkp.txt
10|Administration
20|Marketing
30|Purchasing
40|Human Resources
50|Shipping
60|IT
70|Public Relations
80|Sales
90|Executive
100|Finance
110|Accounting
120|Treasury
130|Corporate Tax
Solution:

In this scenario, Files are created dynamically with the department name so, an
unconnected lookup on flat file (Emp_Dept_Lkp.txt) is created to get the department
name based on department_id.

Informatica mapping to solve above scenario shown below:




Step1:
         -   While creating target, at the last of all ports, select „Add FileName column to
             this target‟ button which is located on Columns Tab and last button.

Step2:
         - You have sort the data in the sorter transformation on department_id.
Step3:
         -   Create aggregator transformation and drag department_id from sorter.
         -   Create a new output port with name Count and put the condition
             count(department_id) in the Expression tab.
         -   Check in the sorted input option in the properties
Step4:
         -   Create an Unconnected Lookup transformation to get the Department name
             on Emp_Dept_Lkp.txt file.

Step5:
         -   Create an Expression transformation to call the above unconnected lookup.
         -   Create an output port with name Department_Name and put the condition
             LTRIM(RTRIM(:LKP.LKP_UNCONNECTED_LOOKUP(DEPARTMENT_ID)))
Step6:
         -   Create Joiner transformation to join Aggregated source and Expression
             source.
         -   Drag department_id, count fields from aggregator to joiner
          -   Drag all the ports from expression to Joiner. And create join condition on
              Department_Id
          -   Check in the sorted input option in the properties.
Step7:
          -   Create Router transformation to Route the data into different targets.
          -   Create groups unique and nonunique
          -   Put the condition count=1 for unique group, count>1 for nonunique group
Step8:
          -   Create an Expression transformation to flag the department_name.
          -   Drag the Router nonunique group ports to Expression
          -   Create variable port with name v_Dept_Name and put the condition
              DEPT_NAME in the expression editor.
          -   Create variable port with name v_Flag and put the condition
              IIF(DEPT_NAME=v_DEPT_NAME,false,true)in the expression editor.
          -   Create an output port with name o_v_Flag and put the condition v_Flag
          -   Create an output port with name o_File_Name and put the condition
              DEPT_NAME||‟.txt‟
Step9:
          -   Create Transaction Control transformation to commit the records when the
              department name is changed
          -   Drag the ports from above Expression
          -   Create Transaction Control condition
              IIF(o_v_Flag=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTIO
              N) in properties tab.
Step10:
          -   Link all the Target ports with the matching Transaction Control ports.
          -   Link FileName port in Target with o_File_Name from Transaction Control

Step11:
          -   Create another Expression transformation to flag the department_name.
          -   Drag the Router unique group ports to Expression
          -   Create an output port with name o_File_Name and put the condition
              Unique||‟.txt‟.
Step12:
          -   Create another Transaction Control transformation to commit the records
          -   Drag the ports from above Expression
          -   Create Transaction Control condition TC_CONTINUE_TRANSACTION in
              properties tab.
Step12:
          -   Link all the Target ports with the matching Transaction Control ports.
          -   Don‟t link FileName port in Target with any port from Transaction Control.

				
DOCUMENT INFO