Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MySQL to MSSQL Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 8:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 556, Visits: 1,634
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?

Post #1513909
Posted Wednesday, November 13, 2013 8:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:16 PM
Points: 301, Visits: 1,276
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
Post #1513916
Posted Wednesday, November 13, 2013 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 5,245, Visits: 12,161
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1513930
Posted Thursday, November 14, 2013 2:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:53 AM
Points: 8, Visits: 45
Alternatively, ConvertDB is able to automate migration. It connects to MS SQL / MySQL Servers located on WIN/Linux directly. Automatic views translation is possible.
Post #1514187
Posted Thursday, November 14, 2013 10:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 556, Visits: 1,634
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.
Post #1514376
Posted Thursday, November 14, 2013 10:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 2,057, Visits: 3,578
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
Post #1514384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse