Load first half records to 1 target while other half records to a separate target

Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.

SOLUTION:

You will have to assign a row number with each record. 

To achieve this, either use Oracle’s psudo column rownum in Source Qualifier query or use NEXTVAL port of a Sequence generator. Let’s name this column as ROWNUMBER.

From Source Qualifier, create 2 pipelines:

First Pipeline:

1.       Carry first port Col1 from SQ transformation into an aggregator transformation. 

2.       Create a new output port “tot_rec” and give the expression as COUNT(Col1). 

3.       Do not group by any port. This will give us the total number of records in Source Table. 

4.       Carry this port tot_rec to an Expression Transformation. 

5.       Add another port DUMMY in expression transformation with default value 1.

Second Pipeline:

1.       From SQ transformation, carry all the ports (including an additional port rownumber generated by rownum or sequence generator) to an Expression Transformation. 

2.       Add another port DUMMY in expression transformation with default value 1.

3.       Join these 2 pipelines with a Joiner Transformation on common port DUMMY. 

4.       carry all the source table ports and 2 additional ports tot_rec and rownumber to a router transformation.

 Add 2 groups in Router: FIRST_HALF and SECOND_HALF. 

Give condition rownumber = tot_rec/2 in FIRST_HALF. 

Give condition rownumber = tot_rec/2 in SECOND_HALF. 

Connect the 2 groups to 2 different targets.