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

Scenario: 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 TransactionControl transformation. Informatica Power Centre allows us to control the roll back and commit on transaction based on set of rows that passes through the TransactionControl 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.

 

Experts, please share your valuable thoughts…..