Scenario 9: How to assign an unique ID for each and every group of records.

Scenario 9: How to achieve target below?

(Source)
ID  | COUNTRY
101 | INDIA
102 | NEPAL
101 | AMERICA
103 | AFRICA
102 | JAPAN
103 | CHINA

(Target)
SID|ID|COUNTRY
1|101| INDIA
2|101| AMERICA
1|102| NEPAL
2|102| JAPAN
1|103| AFRICA
2|103| CHINA

Theory/Analysis
As I have said in the heading we have to assign a unique ID for each and every group of records. Ok, got it Once again we need to compare current records and previous record if matched then increased SID otherwise start SID with 1. So, we have to use once again Expression Transformation and Variable Port. I am in love with these two :-)

Practically

Step 1: As usual Drag and Drop your Source to Mapping Designer.

Step 2:
Sorter
Sort the data based upon ID

ID     key (Checked)

Note: If your Source is relational table you can sort it by overriding Sql.

Step 3:
Expression
Create Six Ports

New_SID (Variable Port)= 1
ID_COMPARE (Variable Port) = IIF(ID!=OLD_ID,ID,OLD_ID)
ID_CREATE (Variable Port) = IIF(ID=OLD_ID,OLD_SID+1,NEW_SID)
OLD_ID (Variable Port) = ID_COMPARE
OLD_SID (Variable Port) = ID_CREATE
SID (Output Port) = ID_CREATE

Note: Please follow order above of ports otherwise you won't be getting expected output.

Step 4: Connect to Target

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