SSIS Lookup with Lookup Component Vs Script Component.
- by Nev_Rahd
Hello,
I need to load Dimensions from EDW Tables (which does maintain historical records) and is of type Key-Value-Parameter.
My scenario is ok if got a record in EDW as below
Key1 Key2 Code Value EffectiveDate EndDate CurrentFlag
100 555 01 AAA 2010-01-01 11.00.00 9999-12-31 Y
100 555 02 BBB 2010-01-01 11.00.00 9999-12-31 Y
This need to be loaded into DM by pivoting it as
key1 and key2 combinations makes Natural key for DM
SK NK 01 02 EffectiveDate EndDate CurrentFlag
1 100-555 AAA BBB 2010-01-01 11.00.00 9999-12-31 Y
My ssis package does this all good pivoting... looking up the incoming NK in DIM.. if new will insert .. else with further lookup with effective date and determine if the incoming for same natural key got any new (change) in attribute.. if so updates the current record byy setting its end date and insert the new one with new attribute value and pulling the recent records values for other attributes.
My problem is if the same natural key comes twice with same attribute in single extract my first lookup which on natural key .. will let both records pass and try to insert.. where its fails. If i get distinct records on NK the second is not picked and need to run package again.
So my question how can i configure lookup or alernative way to handle this scenario when same NK comes twice in single extract, would be able to insert first record if not exists in Dim table and for second one should be able to updated with the changes with reference to one inserted above.
Not sure this makes sense what am trying to explain. Will attached the screenshot once back to work desk (on monday).
Thanks
