Tuesday, 29 October 2013

Generating Dynamic Multiple Target files in Informatica

Recently we came across a scenario to generate multiple dynamic Multiple Target files in Informatica. We receive vendor data through Legacy database in below table


  1. Invoice_ID
  2. Invoice_No
  3. Invoice_Amount
  4. Vendor_Id


We need to separate all details related to one vendor in separate file so that we can pass data to third part vendors in separate file.


INVOICE_DETAILS




INVOICE_ID
INVOICE_NO
INVOICE_AMOUNT
VENDOR_ID
1
A01
100.00
10
2
A02
125.00
10
3
A03
150.00
10
4
B01
200.00
20
5
B02
500.00
20
6
D01
345.00
40
7
C01
230.00
30
8
D02
450.00
40
9
E01
40.00
50






 

 
In order to achieve this we need to follow below steps in mapping


Step1 ) Import INVOICE_DETAILS and drag into mapping and connect all port from Source qualifier to expression and do below sql override


SELECT INVOICE_DETAILS.INVOICE_ID, INVOICE_DETAILS.INVOICE_NO, INVOICE_DETAILS.INVOIC_AMOUNT, INVOICE_DETAILS.VENDOR_ID

FROM

INVOICE_DETAILS

order by VENDOR_ID



Step2) Create expression transformation with as below with 4 input port plus

  1. FILE_NAME (Output Port)  'Vendor_'||VENDOR_ID||'Details.csv'
  2. CHECK_VENDOR_ID(Variable Port)  iif (VENDOR_ID <> PREV_VENDOR_ID,1,0)
  1. OUTPUT_VENDOR_ID (Output)  CHECK_VENDOR_ID
  2. PREV_VENDOR_ID (Variable) VENDOR_ID







Step 3 ) Create Transaction Control Transformation (TC_GENERATE_FILES) with below logic 

iif(OUTPUT_VENDOR_ID=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)



Step 4 ) Create a File Target with same structure as source table and new field in target using option “Add FileName to this Table”

Connect FileName column from transaction control to FileName Port.


Step5) Run the workflow and below files will be created






No comments:

Post a comment