question

I am being asked to load data for the data quality management team. Someone else is extracting the data from the application data source for me. I receive the data as .xls/.xlsx files or sometimes as a delimited file if there are more than 300k records. So when anywhere from 20 to 60 files are handed over to me, I am expected to load the data in a 'few hours' to a half day. I only need to load each file to it's own table (that I need to create on the load). I have used the following methods to load the data and am looking for a most efficient method to turn-around the loading quickly... 1. 32bit SSMS Import/Export tool 'Import and Export Data (32-bit)' 2. VB Module in MS Access, this is slow. Background (the Excel Files are on a file server (UNC location) and whether I run the VB module from the virtual server or my local workstation it loads slowly compared to the 'Import and Export Data (32-bit)' tool in SSMS The virtual server is 64 bit and does not have MS Excel installed for some reason. I was trying to use this: sp_configure 'show advanced options', 1 go reconfigure go sp_configure 'Ad Hoc Distributed Queries', 1 go reconfigure go SELECT * INTO dbo.test_table FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Users\first.last-b\Desktop\File Name.xls;Extended Properties=EXCEL 5.0')...[Sheet1$]; ... but I get this error, Msg 7308, Level 16, State 1, Line 2 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. What is the way to work around it? Is there a way without modding the install on the virtual server. In this communities experience what do you suggest to load data in an enterprise environment where Excel files need loaded as tables? Virtual Server: Window Server Standard, SP2 64bit My Workstation Vista Enterprise 64 bit