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
|
- Sort the source table by the column Location using a Sorter.
- 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.
·
Thank you so much for providing detailed analysis of Informatica and throwing light on such an important aspect.
ReplyDeleteInformatica Read Rest API