BEST way to import from Excel to SQL

[jenlion]

I have a table in SQL that needs to be updated or inserted to based on a given excel sheet.

[b]Table[/b]
Item    Price1   Price2
----    ------   ------
ItemA     3.00     4.00
ItemB     2.99     3.99

[b]Excel[/b]
     A     B         C
1  Item   Price1   Price2
2  ItemA    3.00    10.00
3  ItemB    1.99     3.50
4  ItemC    9.98    25.00

I need to run this from VBScript and it is possible for there to be thousands of entries in the excel file. In the past, I had a script run and line-by-line from the excel sheet and find and update an existing item or insert a new one. This turned out to be much too slow for practical usage. I changed it to a new script that created a linked server, dumped the excel directly to sql, and ran update/insert statements. This was very fast, which is great.

However, now I need two things: 1. speed, and 2. security. The app calling the vbscript has a connection to sql server that I can use, so no name or password need be stored in the app. And you can bet the user running this will not be sysadmin. Linked servers is out because I can't assume the excel file will be saved on a local drive of the sql server and I don't want to hard-code the name and password of someone with those privileges in the script.

Do I have any good options (in sql 2005) for triggering an add/update query from excel -- one that will run very quickly even for thousands of records? (quickly = less than a couple of minutes, preferably less than a minute, for, say, 100k records).

 

[mrdenny]

Do a bulk insert into a staging table, then run a stored procedure to load the data from the staging table into the production table. The user will need to be a member of the bulkadmin server role and have insert access to the table.

You should be able to create a bulk insert object in the VBScript (you can will .NET code, I've got no idea how our .NET guys do it though) and bulk load the data that way.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jenlion]

Is being a member of bulkadmin a security risk at all? I am hoping to avoid user changes to the sql server (other than table permissions) ...

 

[ousoonerjoe]

What about making the database do most of the work? DTS packages. They tend to be much quicker, and you can setup the imports and processing to happen through there instead of having to give out admin permissions. Your script can call the Job and pass the file to it for processing. Making all the work done locally.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[jenlion]

Sounds promising. Can I pass a DTS pkg (or I guess in 2k5 an SSIS pkg? haven't used SSIS much yet) a parameter, like the location of the excel file? The excel file will always be in the same format.

Thanks!

 

[ousoonerjoe]

While I haven't done it personally (in 2005), I did find this via Google:
Working with Excel Files with the Script Task

Another option is to have the script push the file to a single location then call the Package to execute at that location.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[mrdenny]

Yes you can give SSIS packages a file path. If the user is going to trigger the package import they will still need rights to the tables to insert the data. They will also need to have BIDS installed on there workstation, as well as some of the SSIS components to be able to run the package.

The bulkadmin role doesn't actually give the user any admin rights. All it does is allow the user to bulk insert data into what ever tables they have insert rights to.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ousoonerjoe]

Would it not be possible to work around the BIDS install, to execute it from the VBScript by firing a stored procedure that in turn executed the Package? That way there is minimal setup overhead.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[jenlion]

exactly soonerjoe, I don't want to install anything on the client at all...

 

[MDXer]

you can redistribute the dsexec utility which is what actually runs the package. Best way is to have a network share that the users would place their excel files in and the import would run from there. SSIS or BCP users need rights in the DB and are required to run a utility to actually preform the upload.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ousoonerjoe]

That's just it... I don't think jenlion (OP) wants to distribute anything. Thus the use of a script rather than an application. That is why I asked about calling a Stored Proc to execute it on the server side rather than client side.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[MDXer]

yes the user can execute the stored proc but how would the stored proc read in data from an excel file that is probably somehwere on the users pc and not mapped as a share?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ousoonerjoe]

oh no...i wasn't dismissing that part... the excel file would most undoubtedly have to be "pushed" to a common location for processing. In fact, that would make for a self archiving of previous files in the event they are ever needed.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[MDXer]

giving the information in the original post SSIS is going to be your best option. SSIS should be able to accomplish the insert of 100K records in well under 1 minute. The update of the existing record can be done a couple of ways which is best is something that is different for each scenario.

If you want to discuss the actual design of a package o do this then you might want to start a thread in forum1555 which is targeted to SSIS. you will probably get help from the same people but others may chime in or learn from it.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jenlion]

So, it seems I can:
- Set up a location on the server for the excel file to be pushed to
- Call an SSIS package from my vbscript. VBScript call is made by sql user with light permissions. SSIS package points to the excel file and runs stored proc against it.

Is this right?

Thanks all!

 

[ousoonerjoe]

For your purposes, it looks like you need to:
VBScript - Move File to common location on server.
VBScript - EXECute Stored procedure.
Stored procedure - EXECute package.


"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.