Informatica Scenario:To
load the Header records into one target table, say TRGT01, footer records to
TRGT02 and detail records to another TRGT03
Source
File
Header”ABC”
@@@@@@@@@@@@
Detail”123”
Detail”456”
Detail”789”
Detail”012”
@@@@@@@@@@@@
Footer”Efg”
1.Create Source definition
for the file and connect to an Expression
2.In Expression create two
variable ports
- V_HEADER= IIF (SUBSTR(COLUMN,1,1)=’Header’,1,0)
- V_FOOTER= IIF (SUBSTR(COLUMN,1,1)= ‘Footer’,1,0)
Also create two output ports
- O_HEADER=V_HEADER
- O_FOOTER=V_FOOTER
3.Create a Router with two
user groups
- In first group give condition such that IIF(O_HEADER)='1' then pass to TRGT01
- In second group IIF(O_FOOTER)='1' then pass to TRGT02
- And the default group to TRGT3(this will hold the detail records)
Note:
One more option is to use the Header Options in the Session Properties
The simplest way would be to test the value of COLUMN in the group expressions of the router, e.g.
ReplyDeleteUPPER( COLUMN ) ='HEADER'
It might be marginally more efficient to use a DECODE in an expression before the router to set a numeric control port that you use in the router group expressions.
I would also abstract the values in mapping parameters, e.g.
1. define three interger parameters:
p_row_type_header=0
p_row_type_detail=1
p_row_type_footer=2
2. define an output expression
o_row_type=DECODE( UPPER( COLUMN )
, 'DETAIL', p_row_type_detail
, 'HEADER', p_row_type_header
, 'FOOTER', p_row_type_footer
, ABORT( 'unexpected column value [' ||COLUMN ||']'
)
3. define each router group expressions using the row_type and the parameters, e.g.
HEADER: i_row_type=p_row_type_header