Update Or Insert Into SQL Table From Excel Source

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?