MySQL to SQL Server 2005

Hi all,
I have to migrate a MySQL database to SQL Server 2005. What's the best
way to accomplish this? I found the msdn article about migrating MySQL
to SQL Server 2000, but nothing about SQL Server 2005.
I installed MyODBC on the server and made a DSN. I then tried the
Migration Wizard from SSMS (Management -> Legacy -> Data Transformation
Services), but in the Source dropdown list I can only select Microsoft
SQL Server, not MySQL.
Is there any information available on this issue or any hints or tips?
Thanks in advance.
Kind regards,
Ruben.> I have to migrate a MySQL database to SQL Server 2005. What's the best
> way to accomplish this? I found the msdn article about migrating MySQL
> to SQL Server 2000, but nothing about SQL Server 2005.
> I installed MyODBC on the server and made a DSN. I then tried the
> Migration Wizard from SSMS (Management -> Legacy -> Data Transformation
> Services), but in the Source dropdown list I can only select Microsoft
> SQL Server, not MySQL.
Hi!
I've never had to migrate data from any other database to MS SQL
Server, since most of the people I know started off with MS SQL Server
and moved to Oracle ;-)
I'm not sure, but the following is what I can suggest.
You can migrate data from any OLE DB data source using the Microsoft
Business Intelligence Development Studio. An OLE DB driver for MySQL is
available at http://luggle.com/~sean
In the Business Intelligence Development Studio, create an Integration
project and add an SSIS package. Add a Data Flow task to the package by
dragging it from the Toolbox. Then, double-click the Data Flow task to
open another window. Here, drag the OLE DB Data Source twice - one for
MySQL and one for SQL Server. Select the table to move, connect the
output of the MySQL OLE DB Data Source to the SQL Server Data Source.
N.I.T.I.N.|||Hi there NiTiN,
Thanks for your reply, it's much appreciated.
I tried the steps you suggested and it gets me pretty far: I added the
two OLE DB Data Sources in my project and then editted the MySQL one.
I select the MySQL OLE DB provider and fill out the authentication info.
It can connect and get the tables from the MySQL database, but then when
I select a table and hit the "Preview" button I get an exception:
"MySQL OLE DB Provider has not been activated"
And another one:
"Opening a rowset for "bookmarks" failed. Check that the object exists
in the database".
I think that it has something to do with the fact that it's MySQL
version 5 and the OLE DB Provider is pretty old, so it was probably
written for MySQL 3.x or 4.x.
I'll continue looking for some other MySQL OLE DB provider. Other tips
are welcome of course. Thanks again.
Kind regards,
Ruben.|||Update: I decided to just buy Full Convert to do the job for me, since
it's probably cheaper to do it that way. ;)
Thanks for the suggestion though.
Kind regards,
Ruben.|||Ruben van Engelenburg wrote:
> "MySQL OLE DB Provider has not been activated"
Good day,
I know you're moving to Full Convert, but just thought I'd suggest an
alternative for anyone else who decides to come across our thread
sometime in the future.
The MySQL OLE DB Provider has to be activated before use - there's a
link on the same page that leads to the activation page. I'm not sure
if it is limited in any way, but there is an option to get a free
activation. I know I should've mentioned this before, but I thought the
activation link on the web page was close enough to the download link
to attract some attention.
BTW, I just read about Full Convert and it looks like a neat product.
Perhaps I'll try it sometime while migrating between databases.
N.I.T.I.N.|||Hi NiTiN,
Thanks for the note. I sure should have seen that, I can't believe I
didn't. :)
Anyway, I did activate it now just to see if it actually works.
Indeed it did get rid of the activation error when I hit Preview on a
table, but I get a new error instead about the MySQL query syntax being
incorrect. I think it has something to do with the MySQL version, so it
might work with an older version of MySQL, just not with MySQL 5.
I can only confirm that Full Convert is a neat product. It sure saved me
a lot of time and the SQL Server version only costs 129 dollar.
The conversion from a MySQL database to SQL Server 2005 gave me four
errors in a total of about 200000 rows. They all had to do with
constraints, so I have to set those manually afterwards (not a big
deal). I can recommend it.
Thanks again for your helpful tips.
Kind reards,
Ruben.