Home > MySQL, SSIS > MySQL and SSIS 64bit

MySQL and SSIS 64bit

2013/08/04

It was a long time that i was working on MYSQL, and now i needed to transfer data from an MySQL instance to SQL Server.

In some blogs there are samples about, in theory downloading the .NET connector you should see in a ADO Net Source (inserted into an Data Flow Task as in these images) the MySQL item in the .NET providers


 
 

But no MySQL :


This because i’m working on a Windows 7 64 bit , the MySQL provider should be listed working in a 32 bit environment.

This even specifying False for the 64bit runtime (see this post).

I have tried to reboot, etc. but without success.

So i tried the x86, 64 bit msi installer for the ODBC connector, it should be slower than the native .NET provider but…


Note: the installation of the .NET connector causes modifications on the machine.config files (working on ASP.NET 4 for example C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config).
Launching an project from Visual Studio you can hit the error “Could not load file or assembly ‘MySql.Web, Version=6.7.4.0…..”
Edit the maching.config for your ASP.NET version from Notepad (or other text editor) launched as administrator then comment the lines containing references to “MySql”, clear the Visual Studio solution then recompile.

Once installed the ODBC connection , we can define for our Connection Manager an Odbc data provider (see above image)

But before this , in the system Control Panel -> Administrative tools we need to define an ODBC data source by clicking on the ODBC icon.

In the User DSN tab add a new connection


Here we can finally see something related to MySQL…clicking on finish we can see the dialog box:


We need to specify a DataSource Name (possibly meaningful..) and specify IP – port.

The port is tipically 3306, so is needed to verify firewalls (on the local pc and the server which is running mySQL) that this port is not blocked.

By default MySQL has a “root” user with no password, but this in the real case must be veryfied.

Note about the Database name: don’t write it, it seems that causes problems with the ODBC connection , you the must write your SQL with the complete alias for table (prefixed with the db name).

Clicking on Test does not work , if MySQL is not running in the same pc :


We need to give the perms.

On the MySQL instance open the web interface logging as root or other account with GRANT permissions , click on a db name (is not requested to click the specific db) and then on the SQL tab


In this case i created a “test” db (the real db is hidden for privacy reasons) with a Users table and issued the command

GRANT ALL ON *.* TO 'remoteaccount'@'192.168.8.1' IDENTIFIED BY "testpwd";

This creates a new account “remoteaccount” with password “testpwd” (ok, in the real case is better if more complicated) related to the specified IP (see this page).

This time specifying remoteaccount with the related password our connection works:


Now we have a working DSN


to place in our SSIS : add an ADO.NET connection an choose the news DSN:


Note that we cannot have a list of the tables


And also trying to use the Query Builder, no listed tables:


We need to insert by hand the query, eventually trying before on the MySQL web interface.

I inserted a test “Users” table in MySQL ,the interface is easy : the only problem was to understand how to insert an autoincrement value for the ID field, which is done by clicking on the A_I checkbox


So, after a test on the web interface


We can insert the SQL on SSIS


Advertisements
Categories: MySQL, SSIS
%d bloggers like this: