SQL Server Integration Service 2005 – Integration between SQL Server and MySQL
14 Jun 2011
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,
- 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]
- 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]
- 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.
Rishabh Software, a CMMI Level-3 technology company, focuses on cost-effective, qualitative and timely delivered Software Development, Business Process Outsourcing (BPO) and Engineering Services. Contact us today or call 1-877-RISHABH (1-877-747-4224)!
LAMP (Linux-Apache-MySQL-PHP) – A popular Web Application Development Platform
LAMP, abbreviated as Linux-Apache-MySQL-PHP, has become a very popular web application development platform over the last several years. LAMP (Linux-Apache-MySQL-PHP) is still a popular choice for web application development because it provides many benefits over other platforms as below – Cost, Security
Developing Applications with MongoDB Java Integration
Databases are the workhorses for the modern information age. Like Atlas, they go largely unnoticed in supporting the digital world we’ve come to inhabit. It’s easy to forget that our digital interactions like commenting, tweeting & searching are in essence, interactions with a database.
Optimizing Microsoft Azure ClearDB MySQL Database For WordPress
Microsoft is making significant inroads in the cloud infrastructure space. Their focus on ease of use and on demand scalability has made it a preferred choice for companies and developers across the spectrum.
Windows Azure Compute: One of the Five Parts of Microsoft’s Cloud Computing Service
Windows Azure is Microsoft’s cloud computing platform for building Web applications, host and scale through Microsoft data centers. Windows Azure is therefore classified as “platform as a Service” and the part of Microsoft’s cloud computing strategy and the provision of “Software as a Services”.
