SSIS using excel source to insert/update sql table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - SQL Server 2005/Yukon

SSIS using excel source to insert/update sql table

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-29 : 12:03:07

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?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-29 : 12:13:27

Sounds good - can it use a permanent table truncating before the process?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-29 : 12:46:08

@NR: ?

I was probably going to do a Create Table and Drop Table, although creation of static table's for re-use is also possible.

I've found something in another forum that gives me hope that I might be able to do this in SSIS w/o using the create table route. So far, I can do a left join to insert only new rows - I just have to figure out exactly how to do the update piece. See:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=677984&SiteID=1&mode=1

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 04:29:46

You can do everything in ssis but I think the load should always be separated from the processing.
Doing the processing in a stored proc makes it a lot more flexible.
I would only do it all in SSIS if I was working with a company that had little sql server expertise (and didn't want to develop it) but a lot of application devlopement expertise.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-30 : 09:25:10

I think I agrre. It seems like I can do it all in SSIS, but I'm still leaning towards to initial: load into tables, process data via SP kind of idea. Since 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.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources