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:
There are several close dups of this question (Copy access database to SQL server periodically, Migrating Access Tables to SQL Server - Beginner), but none that deal with the 32-bit limitation that makes OPENROWSET/Linked Server a non-option.
I cast my vote for some C# thing. You may need to watch out for memory usage if you have large tables.
The basic idea goes like this:
foreach(tableName in access)
get the table from access
optionally clear the target table
sqlbulkcopy it to target database
A more complicated solution would be to grab both tables and only update the changed rows.