table transfer from MySQL to MSSQL fails

Question

Hi,

I am trying to transfer a table from MySQL to MSSQL using the data migration tool in MS SQL Management studio. I am using .net provider for odbc and selecting the tabel and trying to tansfer. But is is getting the total tables in the database but when I try to transfer it gives error. Even when I try to preview the data, it says,

the preview data could not be retrieved.
Additional Information:
Error [42000].... there is an error near line 1 ( myodbc5.dll).

is there anything wrong?

I downloaded the drivers from MySQL site and I tried reinstalling them over 10 times now.

I am missing anything?

Please help me.

Answer 1

There could be several problems based on your post. I would recommend breaking down troubleshooting to the following steps:

1. Attempt to connect to the server in a tool other than Management Studio and SSIS to see if your ODBC connection works properly. I would recommend using Toad for MySQL by Quest Software. It is free and has a similar UI to Management Studio.

2. Once you connect to the server correctly, try querying the data to verify you have adequate permissions.

3. Do steps 1 and 2 while logged into the server that needs to connect to the MySQL database. You may experience different connectivity problems on the server than on your desktop. For instance, the server may be secured differently on the domain or you may have a driver compatability issue on the server that doesn't exist on the desktop.

4. Try setting up a linked server connection using the ODBC connection. On one server we found we could successfully get this to work but we couldn't get SSIS to play correctly with the ODBC connection. In the worse case, you can use SSIS to execute a linked server query to bring over the data via stored procedure call. Here's a sample Linked Server connection that worked for us in this instance.

   EXEC master.dbo.sp_addlinkedserver @server = N'Some_MySQL_Server_Name', @srvproduct=N'MySQLDatabase', @provider=N'MSDASQL', @datasrc=N'Some_ODBC_Name'

   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Some_MySQL_Server_Name',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

   GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'collation compatible', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'data access', @optvalue=N'true' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'dist', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'pub', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'rpc', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'rpc out', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'sub', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'connect timeout', @optvalue=N'0' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'collation name', @optvalue=null GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'lazy schema validation', @optvalue=N'false' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'query timeout', @optvalue=N'0' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'use remote collation', @optvalue=N'true' GO

   EXEC master.dbo.sp_serveroption @server=N'Some_MySQL_Server_Name', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO

5. The SSIS data flow should use an ADO.NET source. Do not rely on the "table or view" data access mode. Instead, use a SQL command. I've noticed SSIS has problems reading the meta data correctly at times and you may need to CAST some of the fields to resolve this issue. Sometimes it makes the column to long or too short. Pay attention to the data types on the source to see how SSIS may have mucked up the lengths. I don't remember what causes this, but it seemed to relate to nullability and string data types if my memory is correct. I would recommend sending this to a data-reader destination and use the data-viewer to view the data to see if it looks correct. You may also need to add a data conversion step between the source and the destination to convert from unicode to non-unicode.

If you determine the problem is with the ODBC connection, then you may want to try these settings:

1. Flags 1: Return matched rows instead of affected rows; Allow big result sets; Use compression; Treat BIGINT columns as INT columns.

2. Flags 2: Don't promt when connecting.

3. Flags 3: Include table name in SQLDescribeCol(); Disable transaction support. Debug: Log queries to myodbc.sql.

4. SSL Settings: Leave it empty if this is not over SSL.

Clicking Test should return Connection Successful. If this works, then you should be able to get Toad for MySQL to work with it.

If you go the Linked Server route, then make certain the ODBC name matches exactly to the defined data source. I believe we found this was case sensitive, but I'm not entirely sure anymore.