OSIFY

Migrate from MySQL to MS SQL 2005 using MS Access 2003 as a bridge

osify

My friend asked me this topic “Migrate all tables of MySQL to MS SQL 2005 – what way”.
I searched on the net as usual, got many questions and answer around one table import mostly but for many tables seem got a tip from a comment:

This might not be the best solution. Nonetheless, it worked for me. First you need to install and configurate the MyODBC connector as others said previously. Use Access to import from a external ODBC data source. (google it if you have trouble with this step). Once the import is done, use SSIS to import the data in the Access db into a SQL Server 2k5 db. Hope this Helps. (from social.msdb.microsoft.com)

CONTENT:

  1. Technical Environment
  2. Create ODBC Connection for MySQL
  3. Import data from ODBC of MySQL to Access Db
  4. Using SSIS (SQL Server Integration Service) to import data from Access Db to MS SQL

So here is my transforming steps:
0. Technical Environment

I. Create ODBC Connection to MySQL database (using MySQL ODBC Connecter / System DNS)




II. Using MS Access to import data from an external ODBC data source
I’m using MS Access 2007 but I use Access 2003 as the version here as mostly would work for all as well.

Step 1: Create a blank Access Db (Using compatible MS Access 2003)


Step 2: Go to tab External Data, using import or link to an ODBC Database:

Step 3: Using Import the source data into a new table in the current database option

Step 4: Go to tab: Machine Data Source, choose MySQL


“Choose your MySQL database to import”

Step 5: You will see a windows to list all table object, select all and click Ok

Step 6: You’ve done, now. Close button is welcome.

III. Using SSIS (SQL Server Integration Service)
As said above, I’m using MS SQL 2005 Tool (SQL Server Management Studio)

Step 1: start SQL Server Management Studio


Step 2: Connect to the server with your authentication

Step 3: Create new database (in case, it’s new) Ex: mySqlDbLink (not mandatory naming)

Step 4: Right click on the db you want to import all tables / data, choose Import task

Step 5: Choose data source: Microsoft Access and link the file name with above access db (Ex: Z:\LinkToImport.mdb)

Step 6: Follow all steps of importing wizard but here choose: Copy data from one or more tables or views

Step 7: Follow other instruction (select all tables as example), proceed the import


Caution: During my process, I got error and all tables are not imported; I didn’t try to find to know why, but I just try my work for me solution in next comment.

See error log:

– Pre-execute (Error)
Messages
* Error 0xc0202009: {97D241BA-4215-4D82-A8CE-716288708210}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft JET Database Engine” Hresult: 0x80004005 Description: “Unspecified error”.
(SQL Server Import and Export Wizard)

* Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager “SourceConnectionOLEDB” failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

* Error 0xc004701a: Data Flow Task: component “Source 32 – wp_bp_messages_messages” (2627) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

My WORK FOR ME Solution:

  • I again re-import the database with one table and I got successful imported
  • I start again re-import for all tables, and I got successful with all tables list:


Finally: Check at the database, found all tables listed here and check some tables, data are there.

Hope it helps,
Check and give comment if anything to update.
What I have read: