Post reply

SCD2 dataflow

  • December 7, 2011 at 6:18 am

    #247334

    Hi

    We are maintaing SCD2 from source to destination. Pls find the data flow.

    Source to target on (01/12/2011)

    Src.nam | Src.city

    a | a1

    trg.nam | trg.city | createddate | modifieddate

    a | a1 | 01/12/2011 | NULL

    on 2nd & 3rd date we have the following records in source

    src.nam | src.city

    b | b2 (New data created on 2nd)

    b | b3 (city change from b2 to b3 for b on 3rd)

    now if we pass the above 2 records on 4th, both records are showing as active below

    trg.nam | trg.city | createddate | modifieddate

    a | a1 | 01/12/2011 | NULL

    b | b2 | 04/12/2011 | NULL

    b | b3 | 04/12/2011 | NULL

    nam -> business key

    city -> scd2

    Pls suggest how to alter this data flow...

  • December 7, 2011 at 11:51 pm

    #1418485

    The problem is that you are processing two versions of the same business key in the same batch.

    When you do a lookup on your destination, the business key doesn't exist yet, so a new record should be created. SSIS processes your rows in batches, so those two rows are processed at the same time, hence they are both inserted at the same time.

    You should solve your problem earlier. Only select unique values of a business key, and process new versions in a seperate batch.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply