June 9, 2010 at 12:11 am
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
June 9, 2010 at 1:12 am
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