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)

3. OUTPUT_VENDOR_ID (Output)  CHECK_VENDOR_ID
4. 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