SQL Server Performance Forum – Threads Archive

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 24×7 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

---

General Approach for UPSERTs Update T
set col1=S.col1, col2=S.col2,…
from TargetTable T inner join SourceTable S
on T.keycol=S.keycol Insert into TargetTable(columns)
Select columns from SourceTable S
where not exists(Select * from TargetTable where keycol=S.keycol) You can use EXCEL source in place of Source Table eg
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8440 Madhivanan Failing to plan is Planning to fail

---

]]>