Migrate from MySQL (MariaDB) to SQL (Azure)

1

I'm having trouble with this process, and would love some pointers!

Current source database is a MariaDB one (similar to MySQL). I have ODBC drivers connecting successfully.

I've installed Microsoft SQL Server Management (SSMS) 17, and connected to my Azure SQL Database.

I've found solutions that its possible to create a Linked Server in SSMS - but for some reason I cant see this in my Object Explorer?

I've tried to use the SQL Server 2017 Import and Export Tool. Selected the source as .Net Framework Data Provider for ODBC, and entered the name of the DSN.

Select the destination as SQL Server Native Client (also tried Microsoft OLE DB Provider for SQL Server).

First warning thrown up (not sure if this is an issue?) [Source Information] Cannot locate the mapping file to map the provider types to SSIS types

And the big error...

TITLE: SQL Server Import and Export Wizard

Could not connect source component.

Error 0xc0047062: Source - tblBookings [1]: System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [ma-3.0.6][5.5.59-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"tblBookings"' at line 1 at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper100 wrapper)


ADDITIONAL INFORMATION:

ERROR [42000] [ma-3.0.6][5.5.59-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"tblBookings"' at line 1 (maodbc.dll)


BUTTONS: OK ------------------------------*

Happy to try and do this migration another way too. Any tips greatly appreciated.

mysql
sql-server
ssis
mariadb
ssms
asked on Stack Overflow Sep 11, 2018 by Trav • edited Sep 11, 2018 by Shadow

1 Answer

0

Try executing this as root user:

Set global sql_mode=ansi_quotes
answered on Stack Overflow Oct 16, 2019 by user3234166 • edited Oct 16, 2019 by Shree

User contributions licensed under CC BY-SA 3.0