SSIS: Moving Data From MS SQL to MySQL
It seems that lately I've been doing more database work. I'm not a database guru in any sense but the need for data requires me to dive right in. At times it can be interesting especially when using services like SSIS. The request was pretty simple. Specific data that resided on a network Microsoft SQL Server was needed on a public website that was created in Drupal and maintained by a third party. The backend for the website is a mySQL database which is typical for Drupal sites. The database I was to export data to was a separate database that the site could connect to. In this article I'm only covering the data transfer. For this to be production worthy you will need to add error handling and security protocols that are necessary when you transfer data outside your firewall.
What is SSIS?
Microsoft SQL Server Integration Services (SSIS) is a component of Microsoft's SQL Server database software and a platform for building high performance data integration solutions. It is used to extract, transform and load data.
Let us first build a table in the MS SQL and MySQL database. The sample table is shown below.
--Create sample table - MS SQL CREATE TABLE [dbo].[TestTable1]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](20) NULL, [Address] [nchar](30) NULL, [Email] [nchar](100) NULL ) ON [PRIMARY]
--Create sample table - MySQL
CREATE TABLE `client` ( `Id` int(11) NOT NULL, `Name` varchar(100) DEFAULT NULL, `Email` varchar(100) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
After you have created the MS SQL table, we need to put some data in it:
Next we need to download and install the MySQL ODBC connector which can be found here. Go to Control Panel>System and Security>Administrative Tools>Data Sources(ODBC), click on the User DSN tab and click Add.
Select the MySQL ODBC ANSI driver. This will bring up the connector properties box for the connection. Complete the connector dialog box.
Open Visual Studio and select an Integration Service Project. If you don't have this listed in your templates, you may need to download the extension package.
Drag a Sequence Container onto the canvas.
Right click on connection managers, select ODBC, and click Add. Click New. This will be the MySQL connection.
Click on New.
Configure connection similar to what is shown below. Test your connection. Click OK.
To create your MS SQL connection right click connection managers and select OLEDB. Click New. It should be set up similar to what is shown below.
Drag an Execute SQL Task into the Sequence Container. We will be truncating the MySQL table before we insert data into it.
Double click on the task and configure similar to what is below.
Drag a Data Flow Task into the Sequence Container and double click on it.
The Data Flow Task opens up. Drag an OLE DB Source an ODBC Destination onto the canvas. Connect them together.
Double click the OLE DB Source and configure it as shown below and click ok:
Double click the ODBC Destination and configure as shown below and click ok:
Click on the Control Flow tab and connect the Truncate Target task with the Data Flow Task.
Click the Start button in the menu bar to run the project. If you see green checkmarks, the data insert was successful. Check your table in your MySQL database.
This is a very basic connection. As stated at the beginning to make this production worthy you will need to add error handling. Also, anytime you connect outside your network firewall there will a concern for security that needs to be addressed.