I need some advice from the guru's out there

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: FrankT (FrankT_at_discussions.microsoft.com)
Date: 03/21/05 

Date: Mon, 21 Mar 2005 10:41:04 -0800

All,

I've got a problem I need to solve and I'm not sure what the best approach
is. I have a web application that is using basic authentication. We have a
requirement to ingest excel files (very small files) in a standard format,
via the web and error check the contents, then insert the record(s) from
excel into a sql server 2000 database (soon to upgrade to sql server 2005) if
the spreadsheet contains valid data. This has to be done via https and basic
authentication and there is no direct database access from the client machine
to our sql server 2000 database.

I've thought about the following solutions to the problem, but I would like
to leverage anything MS has incorporated into their latest and greatest
toolsets to minimize the amount of code to write.

1. Custom application that would upload the file to the web server, open the
excel file, error check it then insert the record into sql server (lot's of
custom code I think here).
2. Create a web service, and a custom excel interface to talk to the web
service including a button in the excel spreadsheet that would send the data
to the web service, error check it, then insert the record into sql server
(still custom code, but less because we have no file transfer).

Questions:
1. Is there some sort of excel 2003 to SQL Server 2005 ingest capability
that I am missing that I can leverage to acheive the goal?

2. Does anyone have any experience with this, and if so how did you solve
the problem?

3. Any 3rd party components to do this (I couldn't find any)? I've played
with the sql xml interface in sql server 2000 put how do I hook that into
excel?

Any help would be appreciated!!

FrankT

Relevant Pages

  • Re: I need some advice from the gurus out there
    ... a database trigger to insert the contents into the database. ... you will have to write custom code. ... > requirement to ingest excel files in a standard format, ... > to our sql server 2000 database. ...
    (microsoft.public.office.developer.web.components)
  • Re: SSIS and unicode data types
    ... my varchar columns are at most 50 characters. ... destination column in the SQL database destination is only varcharor ... How do I get an Excel column of datatype ... It left SQL Server as a VARCHAR and went into Excel, ...
    (microsoft.public.sqlserver.dts)
  • Use Single Sign on to connect to sql server
    ... connection in an excel file that connects using an odc file connecting using ... The logon account information that you configure here must match the logon ... Name: SQL Server ... If I'm connecting using a sql username, do i need to use the domain piece ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Excel 2007 connection
    ... The Feature pack also contains the SQL Server Client ... You should install it. ... so I installed them both as I do for Excel 2003. ...
    (microsoft.public.sqlserver.olap)
  • Re: Trasfer data to SQL Server
    ... Do you know how you would execute the DTS package from excel? ... Currently Using SQL Server 2000, ... This will place a SQL Server icon onto the DTS ...
    (microsoft.public.excel.programming)