Solved

Data Import to SQL Server 2005 from an access database using C# console application

Posted on 2010-09-07
8
724 Views
Last Modified: 2012-05-10
Hello Experts,

Can somebody help me find a code or some place where i can get the idea of migrating a table from the access database to the SQL server 2005 using a c# console application

REgards
Subbu
0
Comment
Question by:SubbuUSA
  • 4
  • 4
8 Comments
 
LVL 3

Expert Comment

by:dpicco
ID: 33619652
Is there a specific reason why you need to use a C# console application? The best way to do this is using the SQL Server import/export wizard through the SQL Server Management Console.
0
 

Author Comment

by:SubbuUSA
ID: 33619690
I want it to be run as a scripts in my production. It is the customer requirement and that is process being followed in my customer's place. They dont want it to be done via Import/Export wizard. sorry about that.

I have an access1.mdb file and from which i wanted to migrate it to the SQL Server 2005.
0
 
LVL 3

Expert Comment

by:dpicco
ID: 33619898
OK, that makes sense. Just wanted to check and see if you could use the import/export wizard to create a .dtsx package that could be called from the C# application. That definitely seems like the most straightforward way to do this.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:SubbuUSA
ID: 33619944
I am using SQL Server Management studio. I am afraid I do not have import /Export wizard. where I can see the import/export wizard other than this.

Is there a way to do programatically than depending on the migrations wizards..just was curious..

0
 
LVL 3

Expert Comment

by:dpicco
ID: 33619971
In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
0
 

Author Comment

by:SubbuUSA
ID: 33620079
Yes I do have .what I should be doing next..
0
 
LVL 3

Accepted Solution

by:
dpicco earned 500 total points
ID: 33620106
This article explains what to do:
http://msdn.microsoft.com/en-us/library/ms140052.aspx

when you get to step 6, save it to the file system as a .dtsx file which you will call from your C# console application.
0
 

Author Comment

by:SubbuUSA
ID: 33620289
Hello dpicco,

Import /Export route is no acceptable here it seems.

This is my code which i trying to fiddle for a long time..can you help me fixing it.. I have problems right from the connection string.

Appreciate your help
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Runtime.InteropServices;




namespace Access_Data_Migration_Console {
    class Program {
      static private string DestConnectionStr = Application
        
        ConfigurationManager.ConnectionStrings["stafftracker_conn"].ToString();
        static void Main(string[] args) {
            System.Console.WriteLine("Hold on. Access Data Migration Scripts are Running");
            System.Console.ReadLine();
            String Sourcedb = "c:\\StaffTracking_MASTER.mdb";
            String DestConnectionStr = "Data Source=AMTROWSQL05\dev01;Integrated Security=SSPI;Initial Catalog=StaffTracker2";
            String TableName = "EE_Master";

            if (args.Length >= 1) {

                if (args[0] == "/?") {

                    PrintHelp();
                    return;
                }
                Sourcedb = args[0];
            }
            if (args.Length >= 2) {

                DestConnectionStr = args[1];

            }



            if (args.Length >= 3) {

                TableName = args[2];

            }
            
            TransferTableData(Sourcedb,DestConnectionStr,TableName);
        }

        static void TransferTableData(String AccessSourcedb,String DestinationConnectionStr,String TableName) {

            int Start = GetTickCount();
            //Connection to the destination
            SqlConnection DestConnection = new SqlConnection(DestinationConnectionStr);
            DestConnection.Open();
            SqlCommand Cmd = DestConnection.CreateCommand();

            //Connection to source

            OleDbConnection OledbSrcConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + AccessSourcedb);
            OledbSrcConnection.Open();

            // Reader to source

            OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +TableName);
            OleDbCommand.Connection = OledbSrcConnection;
            OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();

            //bulk upload to destination
        
            SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, null);
            bulkCopy.BulkCopyTimeout = 100000000;
            bulkCopy.DestinationTableName = TableName;
            bulkCopy.WriteToServer(OleDbDataReader);
            int End = GetTickCount();
            Console.WriteLine("all rows were uploaded in {0} miliseconds",End - Start);

        }



        [DllImportAttribute("kernel32.dll", SetLastError = true)]

        private static extern int GetTickCount();



        static private void PrintHelp() {

            System.Console.WriteLine("AccessDataTransfer.exe <AccessSourcefile> <SQL server connection string> <TableDef to transfer>");

        }
    }
}

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Configuration Manager WMI Error 11 41
How can I do Unit Testing with Session ? 1 23
Database maintenance 36 109
Securing WEBAPI on Azure 2 27
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question