Scenario 22: How to implement SCD1 along with delete. (Insert, Update, Delete)

Scenario 22: How to implement SCD1 along with delete. (Insert, Update, Delete)

Let's Chat: Normal SCD1 always implemented with only logic of Insert and Update. Means new record inserted in source then insert if any record update in source then update target. What if you have to delete your record in target if any record is delete from source.
There could be multiple approach to achieve this. Here I am opting simple approach with Joiner.

Theory: 
Now it's pretty much clear that we have to implement three operation.
New Record in Source--> Insert
Record Updated in Source-->Update
Record Deleted from Source-->Delete

Ahhh... It's too easy. :-) 

Practically:

My Source Table Name is EMP and Target Table name is EMP_SCD1

Step 1:
Drag and Drop your Source EMP.
Import your target table (EMP_SCD1) as source and drag and drop it in mapping designer.
Drop your target table three times.

Step 2:
Joiner
Drag and Drop all your ports from source 1 EMP to Joiner.
Drag and Drop EMPNO and SAL from Source 2 (EMP_SCD1) to Joiner

Note: For better understanding I have renamed EMPNO and SAL from Source 2 (EMP_SCD1) to EMPNO_TGT and SAL_TGT

Join Condition: EMPNO=EMPNO_TGT

Join Type: Full Outer Join

Step 3
Expression
Create three output ports

New_Rec (Output Port) IIF(NOT ISNULL(EMPNO) AND ISNULL(EMPNO_TGT), 1, 0)

Upd_Rec (Output Port)  IIF(NOT ISNULL(EMPNO) AND NOT ISNULL(EMPNO_TGT) AND SAL != SAL_TGT, 1, 0)

Del_Rec (Output Port)  IIF(NOT ISNULL(EMPNO_TGT) AND ISNULL(EMPNO) , 1, 0)

Step 4
Router
Create three group

Insert_Rec: New_Rec=1
Update_Rec: Upd_Rec=1
Delete_Rec: Del_Rec=1

Step 5
Connect Insert_Rec group to Target Instance 1 for Insert.

Step 6
Update Strategy
Connect EMPNO and SAL from Update_Rec to Update Strategy and from Update Strategy to Target Instance 2.

Update Strategy Expression: DD_UPDATE

Step 7
Update Strategy
Connect EMPNO_TGT from Delete_Rec group to Update Strategy and from Update Strategy to Target Instance 3.

Update Strategy Expression: DD_DELETE

Check List: 

1. Make sure your target table should have a Primary key either Database level or Informatica Level. 
2. Make sure Treat Source Rows as set to DATA DRIVEN at session-->Properties Tab.
3. Make sure Load Type of last two target instance is NORMAL (Though you won't get any error for this. If it is BULK integration service will revert it NORMAL while executing.)

BINGO!!!

Isn't it too easy :-) 

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