Help with Impoting Excel into SQL Server db
-
We have a situation where we get excel files from the clients and the data has to be imported into the database. This is a recurring job. I was looking at options to do this and this is what I tried and the issues:
1. Create a DTS job and run it from my SP using xp_cmdshell - Can't use it because xp_cmdshell needs sysadmin privs to execute
2. opendatasource - can't use it because it needs access to be installed on the db server and our company policy is not to have any other software running on the DB server apart from SQL server.
So I am kind of stuck. I cannot use any option that involves sysadmin or a third party software. Any help is greatly appreciated.
Thanks
-
How about creating your DTS job, creating a SQL Agent job for it with no schedule, and then using sp_start_job to execute?
-
jxflagg- I thought about this option. But was wondering, with this approach, how to I dynamically pass the file name as a parameter to the DTS job?
-
You don't need to pass the filename to the job.
You can setup the DTS package to accept a global variable eg:the file path. Then your DTS package checks if the file exists and loads it.
Checkout the following,
http://www.sqldts.com/default.aspx?246
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
We have a scheduled DTS job that uses the ADO Recordset Object in an ActiveX Script to parse worksheets in an excel workbook and load the data into SQL Server.
The name of the workbook to load is past to the ActiveX Script that does the loading of the file using a Global Variable defined in another ActiveX Script that gets the file name using the FileSystemObject Object.
Caution when using this method:
The data in each distinct column needs to be the same datatype; you can't mix data types in a column, otherwise some of the data will not be loaded. When you interogate the excel worksheet, the columns are checked to determine the datatype for the column you are checking.
See this article for more information:
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q278973
Edited by - garylzon on 12/17/2003 07:11:48 AM
-
To use OpenDataSource, you do not need Access installed, just ADO which should already be there. I use this all the time our SQL Servers with no problems. Here's a sample:
declare @sPath varchar(50), @sFile varchar(50), @tTable sysname, @SheetName varchar(50)
set @sPath= 'c:\samplepath\'
set @sFile= 'MyExcelFile.xls'
set @SheetName = 'MySheet'
set @tTable= 'MyTableFromExcel'
exec('drop table ' + @tTable)
exec('
SELECT*
INTOdbo.' + @tTable + '
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+ @sPath + @sFile + '";
User ID = Admin;
Password=;
extended properties=Excel 5.0'')...[' + @SheetName + '$]')
Edited by - mmortensen on 12/17/2003 4:52:02 PM
-
quote:
jxflagg- I thought about this option. But was wondering, with this approach, how to I dynamically pass the file name as a parameter to the DTS job?If you're having problems with this there is no need to pass this variable in...you can use a table to store it in instead. I usually do this, as I can use the same procs regardless of what I'm using to execute them:
1) Call Controller proc with FileName as a variable.
2) Controller proc enters file name into table
3) Controller fires off SQL Agent Job
4) Step in DTS package retrieves File Name from table and processes accordingly.
Seems more complex, but I've found it works pretty darn good. Also makes code easier to port to new technology (let's face it; SQL Agent isn't that great).
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply