Post reply

From SSIS how to set EndDate when source record is deleted?

  • December 12, 2012 at 12:36 pm

    #286089

    I've created a table in our DW with a StartDate and EndDate column, and when I use the Slowly Changing Dimension option in Data Flow in SSIS (BIDS with SQL 2008) the StartDate and EndDate columns update when data is changed but EndDate isn't updated when the source row in our OLTP database is deleted.

    So for example if I have Account 123, the initial load into DW would be:

    StartDate: 2012-12-12 13:30

    EndDate: Null

    Then I update Account 123 in the OLTP database and now the first row has this:

    StartDate: 2012-12-12 13:30

    EndDate: 2012-12-12 13:35

    and the second row has this:

    StartDate: 2012-12-12 13:35

    EndDate: NULL

    This is all as I expect ... but now if I delete Account 123 from our OLTP database and rerun the SSIS script nothing changes on the last row for Account 123 in DW. I'd expect that EndDate would be updated with getdate() to show it's no longer valid, but this isn't happening.

    Any suggestions on how to do this? I'd have no other way to show this record is no longer in our OLTP system unless EndDate shows it's dropped off.

    Thanks -

    Sam

  • December 12, 2012 at 1:01 pm

    #1568045

    Usually you'd use an audit and/or logging table to move deletions over. SCDs can't autodetect removed information. Trigger your deletions to an audit table and create a separate process to finalize the endDate in the fact table.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

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

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