Using Microsoft SSIS to access MySQL Server's data

This document will show you the steps in a situation when it is necessary to retrieve data from MySQL server into a Microsoft based data warehouse using SSIS, and viewing table schema using Microsoft SQL Management Studio(SMS).

From a Microsoft SQL Server it will access the data in MySQL server through a ODBC connection. A User DSN need to be created for SSIS ETL, and a System DSN need to be created for SMS.

In order to create MySQL DSN it is necessary to get MySQL ODBC drivers.

1. Download MySQL ODBC driver. Go to MySQL website, click on Download. Look for ODBC Connector.
http://dev.mysql.com/downloads/connector/odbc/5.2.html
Select the OS you have then download the client.
Once download complete. Double click on install package file to install it.

2. Create User DSN. After the MySQL ODBC driver installed. Go to Control Panel, Administrative Tools, ODBC Data Source Administrator. On User DSN tab, click Add..
Select MySQL ODBC 5.2 driver
An MySQL ODBC connection window opened. Enter the server name, database, user account and password to set it up.
Give a name of the User DSN connection.
You can the same steps to create System DSN as well.

3. Once a MySQL DSN is created it can be applied on SSIS package for ETL. Using Visual Studio SSIS package, in a Data Flow task, create ODBC source.
In SSIS Toolbox, under Other Sources, drag an ODBC Source into a developer window.
Right click on Edit, on the ODBC Connection manager field, click on New.. button
Click on New.. button again. on the Connection Manager window. click on the drop down box of "Use user or system data source name:" then search and select the DSN you created on step 2.
input user name and password, click Test Connection then click OK.
After that, tables in  the MySQL database is available for ETL by either select a table, view or write a SQL query to retrieve data.