• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

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

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
SubbuUSA
Asked:
SubbuUSA
  • 4
  • 4
1 Solution
 
dpiccoCommented:
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
 
SubbuUSAAuthor Commented:
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
 
dpiccoCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
SubbuUSAAuthor Commented:
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
 
dpiccoCommented:
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
 
SubbuUSAAuthor Commented:
Yes I do have .what I should be doing next..
0
 
dpiccoCommented:
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
 
SubbuUSAAuthor Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now