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