Scenario:
Hello all,
I am new to working with surrogate key columns in database. Recently I developed a workflow/mapping that populates an SCD table with a surrogate key column. For each record that is inserted, I created a logic in expression t/r such that it generates a new sequence number. This seems fine and works OK.
Now, We have a purge logic that runs every day in post-sql that will delete records that have not been updated for the last 10 days. Due to this reason, after testing the ETL process for over 15 days, I find a lot of gaps in the surrogate key column.
Is there a way/logic in Informatica with which I can fill these gaps while loading the target and create a new sequence number only if there a no gaps? Or can this be done at database level? I searched over the Internet but did not find any solution whatsoever.
Please advise.
Solutions:
Hello,
If you can make a bit changes to ur mapping u can achive it.
1. First delete the record which is not been used from last 10 days in per-sql instead of deleting at the end.
2. load the all data in temp table including old and new.
3. Now load all the data in target table with sequence generator.in sg change the setting so that its value reset to 0 for every new run.
OR
Comments
Post a Comment