import excel file to SQL server

Hi all,
I am trying to import excel file to SQL server using web application. I have been browsing all day long trying to get some helps but I found none that really solves my problem. :(
I am aware that I can use DTS in SQL server, however I want to build a web application for it. I can upload the file on the server, my problem is I want to load the data in excel file dynamically; I will need to create a table in the server dynamically everytime I import an excel file.
Please help!!!
Thanks
IrmaHi tinybunny,
Best solution will be
Create table dynamically at application end
and the through DTS transform the data from excel file to table.
You must follow steps to perform this.
Step 1. Create table dynamically at application end.
Step 2. Then pass the dynamically table name and excel file name from application to DTS through dynamic variables.
You have to create stored procedure for this
CREATE Procedure DtsRun
/* Param List */
@.ServerName varchar(30),
@.UserName varchar(30),
@.Password varchar(30),
@.DtsName varchar(250),
@.FileN varchar(200), --File name for excel
@.TblName varchar(200) --Datatable name


AS
DECLARE @.ERROR int -- For Hold Error Number
DECLARE @.CMD varchar(1000) -- Dts Run Command
DECLARE @.DtsPassword varchar(30)
BEGIN
-- Set as No Error
SET @.ERROR = 0

BEGIN
-- With Globle Variable
SET @.CMD ='dtsrun /S '+@.ServerName+' /U '+@.UserName+' /P '+@.Password+' /N'+@.DtsName +' /A strFileName:8='+ @.FileN ' /A strFileName:8='+ @.TblName
--print @.CMD

END

EXECUTE @.ERROR = master..xp_cmdshell @.CMD


END

RETURN @.ERROR
GO
This way you will be passing values to DTS.
Step 3.Create DTS package .
Create dynamic variables for table name and excel file
and then transform data from excel to data table.

|||Hi Nitish,
Thanks a lot for your reply. THAT'S A SUPER GREAT HELP!.. I will give it a try and see if I can get it right...|||Nitish..
I have a question... on step 1, you said that I have to create the table at application end...
The problem is how can I know the column names and the column types (string, int, etc) in excel files since every excel file can have different structure?
Your reply is much appreciated...
Irma|||Excel from version XP/2003 is tabular you can run a query on it but it uses Access SQL not SQL Server version. Hope this helps.|||

Dear Gift,

Thanks for your kind reply. However, I am doing a project right now, and I have to import an excel file to SQL Server for this project. Also, I am new to DTS SQL Server, do you have any recommendations of a good article on how to create a DTS package that would transform excel file to SQL server?
Your help is greatlky appreciated.

|||

Try these links for all your options and I think there is a complete Excel Asp.net solution in the last link from Microsoft. Hope this helps.

http://www.sqldts.com/default.aspx?243

http://www.dotnetjohn.com/articles.aspx?articleid=54
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/Office_Excel_XML_Toolbox.asp
http://blogs.msdn.com/vsto2/

|||Thank you Gift!!!