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:

 tabledata

 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. 

 mysqlconn0

 Select the MySQL ODBC ANSI driver. This will bring up the connector properties box for the connection. Complete the connector dialog box.

mySQLConn1

 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.

NewProj1

 Drag a Sequence Container onto the canvas.

seqCont1

 Right click on connection managers, select ODBC, and click Add. Click New. This will be the MySQL connection.

connMgr1

Click on New.

connMgr2

 Configure connection similar to what is shown below. Test your connection. Click OK.

connMgr3

 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.

connMgr3a

 Drag an Execute SQL Task into the Sequence Container. We will be truncating the MySQL table before we insert data into it.

exeSQLTask1

 Double click on the task and configure similar to what is below.

exeSQLTask2

Drag a Data Flow Task into the Sequence Container and double click on it.

DataFlowTask1

The Data Flow Task opens up. Drag an OLE DB Source an ODBC Destination onto the canvas. Connect them together.

DataFlowTask2

 Double click the OLE DB Source and configure it as shown below and click ok:

 OLEDBSource1

 Double click the ODBC Destination and configure as shown below and click ok:

ODBCDest1

 Click on the Control Flow tab and connect the Truncate Target task with the Data Flow Task.

ControlFlowTab1

 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.

ExecuteContainer1

 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. 

Author: Christian Apostolu
Christian Apostolu's picture
About:

Owner/founder of Crush The Soul, software developer, amateur athlete, frustrated musician. 

"Once we accept our limits, we go beyond them." - Albert Einstein

 

Category: