We need to process approximately 1000 MSAccess (JET) databases on a weekly basis. We need to pull data from certain tables in the database and into "load" tables in our data warehouse on Sql Server 2008(64 Bit).
Currently, since there isn't a 64 bit driver in Sql Server 2008, we installed an instance of Sql Server 2005 (32 Bit), and created a linked server to access the Jet database. We then are issueing sql commands to insert the data into the sql 2008 tables -- Insert Into Sql2008Table From LinkedServer...TableName
While this works fine, its is extremely slow. I'm trying to find a better way. Before we upgraded, we had a DTS package which ran in about 4 1/2 hours to import all the data. Now, doing it in this fashion has doubled the load times.
How else can we get this data into Sql 2008? What is going to be the fastest and most stable way? Is it better to create an SSIS package that can be called from a Stored Proc or a Job? Is it better to export the data to a flat file and bulk insert? What's the best way to export the data to a flat file from jet?
Answer by TimothyAWiseman ·
Idstam has a good answer, but an alternative you may wish to consider is to set up a separate machine to preprocess the data.
You could import it into 32 bit instance of SQL on the other machine, and then very quickly and easily transfer the preprocessed data over to your main machine. This is especially useful if there is a substantial amount of processing involved in the import rather than simply copying the data from the Access tables to SQL tables.