Copy data from an SQL Server 2005 database to Access 2007 database with a different schema in C#

Question

I have data in an SQL Server 2005 database which I need to copy to an Access 2007 database. It is a database conversion tool. Essentially each table corresponds to a table of a different name and within each table each column needs to be mapped to a corresponding column in the Access 2007 database.

Just wondering what is the easiest way to acheive this in C#. I would like to write as few SQL statements as possible.

Things I have considered:

• "INSERT INTO ... VALUES ..." SqlCommands in loops with parameters
• Filling a DataTable for each table in each database and adding NewRows to the Access table
• Using DataTableMappings

Essentially I would just like to be able to specify the mappings of source table/columns to destination table/columns and have it perform the conversion automatically.

What I am looking for is the simplest way to achieve this, and if there is a class that does this already.

Thanks.

Answer 1

The easiest way to do this in C# is not to do it in C#. Of course you can, but that is like driving in a nail with a screwdriver. Try SQL Server Integration Services (SSIS). It is made to do this type of data extraction, translation and loading. If you must do it in C#, use C# to shell out to your SSIS package.