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...
The virtual server is 64 bit and does not have MS Excel installed for some reason. I was trying to use this:
... but I get this error,
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
This issue sounds like is related to 32bit Excel drivers being used on a 64bit system. You'll need to switch one to match the other ie Excel 64bit installation or run it on a 32bit server I think.
answered Aug 15, 2012 at 07:55 AM
Jonathan Allen, as usual, is completely right about why OpenDataSource isn't working.
But if the goal is to load a large amount of data, you could consider using SSIS. It is designed specifically for that and works quite well. It is also highly scriptable and can be scripted through BIDS.
answered Aug 28, 2012 at 06:27 PM