I have an ancient system that uses an Access 97 database to store information. I want to copy the data from the 90-some tables to a SQL Server 2008 database on a daily basis. I already have the tables defined in SS2008.

There is an equally ancient DTS job that has a separate box-line-box for each table. I'd rather use an easier to maintain method that was written in code, not lines and boxes. (yes, I know that SSIS lines and boxes are translated into XML, but that's kind of hard for me read and write.)

I can't use Linked Server or OPENROWSET because my SS2008 server runs as a 64-bit process, so the OLEDB Jet driver is not available. The OLEDB MSOffice ACE 12.0 driver is 64-bit, but it isn't supposed to be used with database servers because it is not threadsafe (according to Microsoft). Also, I can't get it to work ("Could not find installable ISAM") within SS2008 despite extensive research. I can read the Access table with OLEDB Jet in a 32-bit program such as SSIS.

So, I'm looking for a modern, non-box-and-line, elegant 32-bit solution to copy the tables from the Access mdb/mdw file to SS2008.

Can I do this with:

a single T-SQL script

some C# thing that does introspection to determine table structure and then executes SQL for each table

some magic "copy every table from this OLEDB to that SQL Server" package

thanks for the pointer, I had not seen that in my research. unfortunately, to use that solution in this instance I'd have to generate 90 such box-line-box routes. I'd rather a solution where I can loop through the tables and pull from Access and push to SQL (since the schemas are identical)
–
patrickmdnetJun 16 '13 at 13:07