Scenario 18: How to restrict total no. of records to load into target based upon session run.

Scenario 18: How to restrict total no. of records to load into target based upon session run.

CASE: In source EMP table we have 14 records and we have to load only two records for each run. Means when first time session ran then load first two records then upon second run next two records. So, finally on seventh run of session all records will be loaded to target.

Theory: By analyzing the case we can easily conclude that we need to keep a counter of session run which will be incremented by 2 for every run and If the record id is greater than previous load id and less than equal to session counter then we are done.

Practically:
Step 1: Drag and Drop your source and Target

Step 2: Create two mapping variable
$$Sess_Counter (Integer)
$$Prev_load_ID (Integer)

Step 3: 
Expression
Create Three Ports
Rec_ID (Variable Port)= Old_Rec_ID + 1
Old_Rec_ID (Variable Port)= Rec_ID
Out_Rec_ID (Output Port)= Rec_ID

Step 4:
Filter 
Filter Condition: OUT_REC_ID > $$Prev_load_ID AND OUT_REC_ID <= $$Sess_Counter

Step 5:
Expression
Create one Port
Prev_load_value_Assign (Variable Port) = SETMAXVARIABLE($$Prev_load_ID, OUT_REC_ID)

Step 6
Connect your actual source columns to target.

Step 7
Create Non-Reusable session

Step 8
Create Workflow Variable
$$Sess_Counter_Value (Integer) Persistent (Check box should be checked)
Default Value=2

Step 9
Create Assignment Task
Develop Expression
$$Sess_Counter_Value = $$Sess_Counter_Value + 2

Step 10
Double Click on your session-->Components Tab
Pre_Session_Variable_Assignment
$$Sess_Counter = $$Sess_Counter_Value

Step 11
Create a Link condition which connects from session to assignment task.
$S_RESTIRCT_LOAD.PrevTaskStatus=Succeeded

What are you waiting for Run it Analyze it :-)

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