MySQL to MSSQL

Question

MySQL to MSSQL

dan-572483

SSCoach

Points: 15870
More actions
November 13, 2013 at 8:01 am

#301095

What is the best approach for extracting data from a MySQL Database and importing it into MSSQL? 1) export to text file then import, 2) Set up ODBC conneciton to the MySQL server as a Connection Manager item - can you specify a query in a dataflow task that is correct for MySQL but won't run in MSSQL? or 3) Some other way?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 1

Making use of Microsoft SQL Server Migration Assistant(SSMA) for MySQL is the best bet as the starting point IMO. I used SSMA for Oracle very recently with fruitful results.

You can find this tool for free from Microsoft site here:

http://www.microsoft.com/en-us/download/details.aspx?id=28764

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

dan-572483 (11/13/2013)
What is the best approach for extracting data from a MySQL Database and importing it into MSSQL? 1) export to text file then import, 2) Set up ODBC conneciton to the MySQL server as a Connection Manager item - can you specify a query in a dataflow task that is correct for MySQL but won't run in MSSQL? or 3) Some other way?

Your option (2) is correct also. Whether the source query runs OK on SQL Server is not relevant if your connection is to MySQL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

dm-972730 SSC Veteran Points: 248 More actions · Answer 3

Alternatively, https://dbconvert.com/mysql/mssql/ automates migration tasks. It connects to MS SQL / MySQL Servers located on WIN/Linux directly. Automatic views translation is possible.

This reply was modified 5 years, 7 months ago by dm-972730.
This reply was modified 3 years, 11 months ago by dm-972730.
This reply was modified 3 years, 11 months ago by dm-972730.
This reply was modified 3 years, 11 months ago by dm-972730.

dan-572483 SSCoach Points: 15870 More actions · Answer 4

This isn't a migration - at least after the initial pull. There's going to be a daily extract of records created the previous day in a MySQL-hosted system which are inserted into a MSSQL database.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Hi Dan,

I've done option 2 successfully in the past, also a 3 - create a linked server in MS SQL & query/import the data through that.

I found 3 a bit better (and gave more useful info for debugging), but then I'm happier in SSMS than SSIS.

Cheers

Gaz