?
Solved

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

Posted on 2010-09-07
8
Medium Priority
?
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

800 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