Scenario 2 Split Total Number of Records in two parts and load it based upon session run.

Scenario 2 Split Total Number of Records in two parts and load it based upon session run.

Ex: Emp table has 14 records. On first time session run 7 records should be loaded and on second time run remaining 7 records should be loaded.
In other words we can say on each run only 7 records should be loaded.

Solution

Theoretical

We need
AA) We need Mapping Variable to keep counter of Session run.
BB) We need Workflow Variable to keep counter of Workflow Run.
A) We need two Instance of Source. One as Actual Source to load data from it and Second Source for Aggregator to Count total number of records in Source.
B) In Aggregator Count total number of records. Create one dummy port for joiner condition. Don't select any group port.
C) In Expression1 create one dummy port which we will be using joiner to get the total number of records for each and every row.
D) In joiner connect both pipeline based upon DUMMY column.
E) In Expression 2 Assign an ID for each every record passed through it and find the value of half. Ex: Total_Number_Records(Which returned by Aggregator)/2
F) In Router based upon Mapping Variable and Record_ID which is less/greater than Total_Number_Records/2 pass records.
G) Create Non Reusable Session so, that you can use Component tab PreSession Variable Assignment Option.
H) Assign Workflow Variable Value to Mapping Variable
I) Create Assignment Task and Increment or decrement workflow variable based upon current value of workflow variable.
G) Need to set link condition also between session and assignment task. (If prev task status is succeeded then only pass execution otherwise stop)

Practically:

Create One mapping Variable ($$TGT_HIT) Integer, Aggregetion=(Count) No need to initialize it.
Create One Workflow Variable ($$Sess_Run) Integer (Persistent) Default Value= 1.

Step 1 Drop your source twice for two pipeline.

Step 2
Aggregator
create two ports.
     Total_Record (Number)= Count(EMPNO)
     DUMMY  (Number) 0
Note-- We need this DUMMY port for Joiner Transformation.
     Don't Select any port as GROUP port.

Step 3
 Expression 1
 Create one Output Port.
     DUMMY  (Number) 0

Step 4
Joiner Transformation
     Join Type: NORMAL
     Condition: DUMMY=DUMMY1
Note: By joining based upon dummy port we will get Total_Record count for each and every record.

Step 5
 Expression 2
 Create Four ports.
     Rec_count  Number (Variable Port)= Old_count +1
     Old_Count  Number (Variable Port) = Rec_count
     Rec_ID  Number (Output Port)= Rec_count
     Half_Rec Number (Output Port)= ROUND(Total_Record / 2)

Step 6
 Router 
 Create Two Group
     First_Half: ($$TGT_HIT=1) AND (Rec_ID < = Half_Rec)
     Second_Half: ($$TGT_HIT=2) AND (Rec_ID > Half_Rec)

Step 7: Connect both group to same target instance or different target according to your requirement. (For better understanding I have taken two different Target)

Step 8 
Create Non-Reusable Session.
     Component Tab-->PreSession Varaible Assignment--> Create one field and set
      Mapping Variable/Parameter = Workflow Variable/Parameter
                 $$TGT_HIT      =   $$SESS_RUN

Step 9
 Create Assignment Task: Develop expression as below.
     $$SESS_RUN=IIF($$SESS_RUN=1, $$SESS_RUN+1, $$SESS_RUN -1)

Step 10 We also need to set link task as: PrevTaskStatus=SUCCEEDED

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com