SQL2008 SSIS - Update and Insert

  • Hi

    I am in need of help to build a SSIS package to update a customer table.

    The database captures all change history, so it's a bit more complicated than just updating records. 🙁

    On a periodic basis, I will receive a customer file which I upload into a staging table, and then update the customer table.

    The fields in the staging table only contain a subset of the fields in the customer table.

    Each customer record has a customerid, current/not current flag and record sequence.

    What I need to achieve:

    Match the staging table to the customer table on customerid.

    If the customer does not exist in the customer table, then insert a new customer record.

    If the customer does exist, and no details have changed then do nothing.

    If the customer does exist, and any details have changed then do 2 things:

    1. in the customer table, change the current record to be not current

    2. duplicate that record to the next sequence, update the details, and make current.

    I have tried a number of things including lookups and multicast, but have not yet found the right combination to achieve this.

    Any advice on how to do this is most appreciated. I am shy with using script task, so if this is part of the solution, I may need a bit of detailed help.

    Alternatively, if this is better off in t-sql, then I would also like to hear about that.

    Thanks for your assistance.

    K

  • You need to investigate the SCD Component.

    Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
    Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
    Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

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

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