Solved

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

Posted on 2010-09-07
8
730 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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