Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-07
8
Medium Priority
?
735 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 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