SSIS using excel source to update/insert sql table

Hi all,

I've had little success gooling/searching for this (so far).

Given a simple spreadsheet:
StoreNumber StoreName
1 UPDStoreName_1
2 UPDStoreName_2
3 UPDStoreName_3
4 NEWStoreName_4

I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))
StoreNumber StoreName
1 StoreName_1
2 StoreName_2
3 StoreName_3
5 StoreName_5

.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :

StoreNumber StoreName
1 UPDStoreName_1
2 UPDStoreName_2
3 UPDStoreName_3
4 NEWStoreName_4
5 StoreName_5

(the UPD and NEW are added to simplify the example).

Now the default action of an excel source into an ole db destination is an insert into the table - so PK constraints causes failures.

Now, given that the table is referred to by other table, and is in a 24x7 website, how do I change the SSIS package such that, on a row-by-row basis, anUpSert (update or insert) is performed?

The only idea I have so far is:

create temp table
insert excel data into temp table
iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP

Isn't there a better way?



Panic, Chaos, Disorder ... my work here is done --unknown