During my current project i must transfer data Every Single Day from MS Use of Sql Server 2008 standard edition. obviously i'm able to use SQL Migration tool to change Access database into Sql Server database, but issue is i must do that over and over every single day to help keep upgrading my sql server database.

Same with there in whatever way i'm able to write C# program that transfer data from MS ACCESS file to SQL SERVER?

Any help could be highly appreciated.

Many Thankx

Vijay Shiyani

As recommended by Serkan in the comment, I'd opt for using SSIS (SQL Server Integration Services) with this task especially when you are already using SQL Server and SSIS is build exactly to aid this type of task.

SSIS is extremely flexible (like the majority of ETL packages) and you will add custom logic for transformation of information (if any)

Furthermore, should you produce a job in SQL server, you'll be able to schedule this which job will execute the SSIS package that connects towards the MS Access database and employ it like a databases instantly.

Furthermore, you have the choice to call an SSIS package from C# code (if for whatever reason you've got a requirement not to use scheduled jobs but have to provide some type of interface for customers to start the job) using DTExec.exe or using the classes uncovered via Microsoft.SQLServer.ManagedDTS.dll.

Refer this link for the best way to invoke an SSIS package from C# code

I recommend an ETL (see Talend Open Studio for instance). You design a transfer job (with appropriate transform if required) after which make use of your OS task scheduler to operate the task every single day.

Note: Talend Open Studio creates java or perl code.

Why don't you just migrate the information permanently to SQL Server and employ your Access application because the front-end to some SQL Server database rather than a Jet/ACE database?

You might run update/remove/append queries inside C#. With varous IN clauses pointing towards the databses under consideration. Exactly how you'd do that in C# I'm not sure however these could be simple to do in Access.

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

To recognize a resource table:

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

from Access 97 help.