How to load single source data in four different target based upon session run through single mapping.

Scenario 1: How to load single source data in four different target based upon session run through single mapping.

Ex: One Source (Emp Table)

      1st run of Session-->EMP_TGT1

      2nd run of Session-->EMP_TGT2

      3rd run of Session-->EMP_TGT3

      4th run of Session-->EMP_TGT4

      5th run of Session-->EMP_TGT1

Solution:

Theoretical 

We need 

AA) Mapping Variable to keep counter of mapping run.

A)    Two Instance of Source: First instance treat as actual source for loading data. Second   instance of source for Aggregator Transformation.

B)    Aggregator  to find total number of records in source. As we are not selecting any port as group by so, it will return last record with Total Number of Records.

C)    Joiner to Join both Source Qualifier pipeline based upon EMPNO as It's MASTER OUTER   type of join so, it will return matched record from master and all records from outer table.

D)    In Expression keep counting each and every records passed through it. When matching of   Count in Expression is matched with Total Number of Records then increment Mapping   Variable Counter with 1.

E)     Route records based upon Mapping Variable counter value.

Practical

1. Create Mapping Variable $$TGT_HIT (Integer) Aggregation (Count) -->Initialize with 1 (Initial Value=1)

Step 1: Drop your Source twice.

Step 2: Create Aggregator and connect Second Source Qualifier to it.

Aggregator

A) Create one port Total_Record (Output Port) COUNT(EMPNO)

B) Don't select any port as Group By port

Step 3: Create Joiner and connect First Source Qualifier to it.

Joiner

A) Connect all column from First Source Qualifier to Joiner

B) Connect Total_Record and EMPNO column from Aggregator

C) Set Join Type=MASTER OUTER JOIN

D) Condition: EMPNO1=EMPNO

Step 4: Create Expression and Connect all columns from Joiner to it.

Expression

A) Create Four ports

v_rec (Number) (Variable Port)-->v_rec_count + 1

v_rec_count (Number) (Variable Port)-->v_rec

Var_Assign (Number) (Variable Port)-->

IIF(Total_Record = v_rec_count AND $$TGT_HIT = 1, SETVARIABLE($$TGT_HIT, 2), IIF(Total_Record = v_rec_count AND $$TGT_HIT = 2, SETVARIABLE($$TGT_HIT, 3), IIF(Total_Record = v_rec_count AND $$TGT_HIT = 3, SETVARIABLE($$TGT_HIT, 4), IIF(Total_Record = v_rec_count AND $$TGT_HIT = 4, SETVARIABLE($$TGT_HIT, 1)))))

NEXTVAL (Number) (Output Port)-->$$TGT_HIT

Step 5: Create Router and Connect all columns from Expression.

Router

A) Create Four Groups

FIRST_TARGET=NEXTVAL=1

SECOND_TARGET=NEXTVAL=2

THIRD_TARGET=NEXTVAL=3

FOURTH_TARGET=NEXTVAL=4

Step 6: Connect each group to each target.

Create Session and Workflow and BINGO!!!!