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