How to transfer data from Microsoft Access to SQL server 2008?

Question

Hi friends,

In my current project i have to transfer data EVERY DAY from MS Access to Sql Server 2008 standard edition. of course i can use SQL Migration tool to transform Access database into Sql Server database, but problem is i have to do this again and again every day to keep updating my sql server database.

SO is there any way i can write C# program that transfer data from MS ACCESS file to SQL SERVER?

Any help would be highly appreciated.

Many Thankx

Vijay Shiyani

Answer 1

I would suggest an ETL (see Talend Open Studio for example). You design a transfer job (with appropriate transform if necessary) and then use your OS task scheduler to run the job every day.

Note: Talend Open Studio generates java or perl code.

Answer 2

As suggested by Serkan in his comment, I would go with using SSIS (SQL Server Integration Services) for this task especially as you are already using SQL Server and SSIS is build exactly to support this kind of task.

SSIS is very flexible (like most ETL packages) and you can add custom logic for transformation of data (if any)

Additionally, if you create a job in SQL server, then you can schedule this task and this job will execute the SSIS package that connects to the MS Access database and use it as a data source automatically.

Moreover, you do have the option to call an SSIS package directly from C# code (if for some reason you have a requirement to not use scheduled jobs but need to provide some kind of interface for users to kick off the task) using DTExec.exe or by using the classes exposed via Microsoft.SQLServer.ManagedDTS.dll.

Refer this link for how you can invoke an SSIS package directly from C# code

Answer 3

Why not just migrate the data permanently to SQL Server and use your Access application as the front end to a SQL Server database instead of a Jet/ACE database?

Answer 4

You could also run update/delete/append queries inside C#. With varous IN clauses pointing to the databses in question. How exactly you'd do this in C# I don't know but these would be easy to do in Access.

[SELECT | INSERT] INTO destination IN
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN 
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

from Access 97 help.

Answer 5

You might want to check out DBSync solution which is able to synchronize data between Access and MSSQL databases daily.