SQL Server Integration Service 2005 – Integration between SQL Server and MySQL

This article aims at relaying the steps in integration between SQL Server and MySQL using SQL Server Integration Service 2005

Following are the steps for integration.

1. Install MySQL ODBC Driver

To connect MySQL from SQL Server Integration Service 2005 requires the installation of the correct MySQL ODBC Driver. You may find these drivers from the MySQL website http://www.mysql.com/downloads/connector/odbc/

After Installation you will find drivers installed in Control Panel -> Administrative Tools -> Data Sources (ODBC)

2. Create SQL Server Connection Manager and MySQL Connection Manager

Creating SQL Server Connection Manager is easy. You can create SQL Server connection manager with OLE DBSQL Native Client and provide credentials.

For MySQL you need to create a ne.w ADO.Net Connection and select .Net Providers Odbc Data Provider from the list as outlined below,

1. You may use DSN or a Connection string[I prefer the connection string over DSN as moving package from one server to another server will not require to create new DSN]
2. You may use the following connection string if you have not created DSN“Driver={MySQL ODBC 5.1 Driver}; server=your_server_name; uid=db_user_id;database=database_name;port=port_number;”[Note: Use your version of driver]
3. Enter user id and password in respective textbox

Now you are ready with both connection managers.

• Create Data Flow Task – Select dataflow component from toolbox to control flow area 
• Configure Data source – On dataflow area for dataflow task we first need to select Oledb Data Source from toolbox 
• Configure Script Component – For destination a script component needs to be configured which will do the job of inserting records in MySQL.
  • Select  script component in toolbox
  • On advance setting on right click- select MySQL connection manager and give  any name and hit finish.
  • On Edit on right click go to design script- where new .net code behind window will open with an event – Input0_ProcessInputRow

Following is the code for script component which calls upsert stored procedures to insert/update record in the MySQL product table.

Now you are ready with package wich fetches data from SQL Server and inserts in MySQL Database.