Tuesday, July 3, 2018

How To Generate Dynamic Target File In Informatica Based On Column Value

Generate different flat file target based on the Location name, like separate files for Mumbai.dat, Bangalore.dat, and Delhi.dat

Source Table:

Dept name

Dept ID

Location

DWH

1

Mumbai

Java

2

Bangalore

Dot net

3

Delhi


  1. Sort the source table by the column Location using a Sorter.
  2. Create Expression transformation and create the below ports

IN_LOCATION=LOCATION
V_FLAG=IIF(IN_LOCATION=V_LOCATION,0,1)
V_LOCATION=IN_LOCATION
OUTPUT_FLAG (OUTPUT PORT) = V_FLAG
OUTPUT_FILE_NAME (OUTPUT PORT) = LOCATION ||'.dat'


**** This expression will check when Location changes (Eg.from Mumbai to Bangalore) ****  

3. Now we need to connect the Expression Transformation to Transaction Control transformation. Informatica Power Centre allows us to control the roll back and commit on transaction based on set of rows that passes through the Transaction Control transformation. This allows to define your transaction whether it should be committed or rollback based on the rows that pass through, such as based on the Entry Date or some other column. 

Use the following syntax for the expression:


IIF (CONDITION, VALUE1, VALUE2)
IIF(OUTPUT_FLAG = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

****This expression will write to the output file when location changes (Eg.from Mumbai to Bangalore)

4.  Connect to the Target Table

Note: We have to use special port called “Filename" port in the Target file definition.


·

1 comment:

  1. Thank you so much for providing detailed analysis of Informatica and throwing light on such an important aspect.

    Informatica Read Rest API

    ReplyDelete

Thank you :
- kareem