Using excel source to insert/update SQL table

Question

Using excel source to insert/update SQL table

Regan Galbraith

SSCertifiable

Points: 5227
More actions
August 29, 2006 at 10:03 am

#119428

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?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 1

If you use the temporary table to delete existing, then do an insert, you should be able to avoid row by row logic.

If something is deleted out of the Excel sheet, it will persist on the server. Usually fine for data warehousing, but not always the desired result.

GE

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 2

Hi Gregg,

Thanks for the response.

The spreadsheet data will only be used for new/changed riows - we don't want to delete existing rows that are not in the spreadsheet. Additionally, because we have RI based on those tables, and those tables are parents, we can't delete data and then re-insert. finally, we want the locking to be as fine-grained as possible/as fast as possible, since the tables be altered are part of the data in use by a high transaction, 24x7 web site.

I've found a way to do the initial problem in SSIS, by making use of the following:

----------------------------------DATA FLOW----------------------------------

Excel as data source -> data Conversion(to data tyope for table) ->Sort -> Merge join as left join component

Table as data source -> Sort -> Merge join as 'right' table

Join on the primary key to the excel, and include the table primary key as part of the resultset.

Merge Join -> conditional split

conditional split (where tablePK in result in Merge join is null) -> Table Destination (insert, since null table key = new rows)

conditional split (where tablePK in result in Merge join is NOT null) -> OLE DB Command using column names to replace parametre's in an update statement.

----------------------------------

This does an update for rows that exist, based on PK match to the excel spreadsheet, and an insert for rows that don't exist.

Now I just have to modify this to cater for the fact that the spreadsheet I'm ghoping to get has data for 2 tables, and I need to do a lookup in the middle to get a foreign key ID.