In my previous article I had created linked server, to access and query a database. However, it is just the basic step to try and bring two different RDBMS systems linked together.
In this article I will discuss about creating a SSIS package to transfer data from MS SQL Server 2008 to MySQL 5.5
Prerequisite:
___________________________________________________
SQL Server Business Intelligence Development Studio
MySQL Connector Net 5.2.7
You can download this package from http://www.mysql.com/downloads/connector/net/
Connector/Net is a fully-managed ADO.NET driver for MySQL.
MySQL Connector/ODBC 5.1 (Which is already installed, in our previous article)
http://www.mysql.com/downloads/connector/odbc/
Brief about SSIS
___________________________________________________
SSIS provides a graphical front end to design control flow data processing logic. Once designed, these ‘packages’ are compiled into ‘.dtsx’ packages which can then be distributed and run on any machine with the SSIS tools installed.
Packages contain two main logic flows, a ‘Control Flow’ which defines a sequence of logical operations which are processed in sequence. Each step is completed before the next starts e.g.
1. Empty out work tables in a database
2. Populate the work tables with data
3. Perform calculations and update the values in the work table
4. Update OLAP cubes with the data from the work tables
5. Run reports against the OLAP cubes.
This level of control also allows processing loops to be defined e.g.
For each file in a specified folder, read the contents of the file and write it into a specified table.
The second main logic flow is the ‘Data Flow’. This allows for the processing of data at the record level. Data is read from a ‘Data Source’ and passes down a series of ‘Data Transformations’ to a ‘Data Destination’. These transformations can be as simple as changing the data type of fields e.g. varchar(4000) to varchar(2000) or decimal(18,2) to decimal(8,2), or can be more complex like data merges, joins, pivot tables, multicasts etc. Each transformation is represented by an icon in the designer and the icons are linked together to define the logic path.
Creating SSIS package to transfer data from MS SQL Server 2008 to MySQL
___________________________________________________
Follow the below steps to create a SSIS package for data transfer.
Go to START>MS SQL SERVER 2008>SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO and Click on File>New>Project
Under Business Intelligence Projects, select Integration Services Project and rename the Project Name.
This will open a Package Designer Screen, where you can start designing the data flow for your package.
Configure Connection Managers for you package
___________________________________________________
Right Click in the area where it shows the Connection Manager tab and select New ADO.Net Connection.
You will see the Configuration Manager Editor window
Click New
Now you need to define connection manager configuration for source (i.e. MS SQL Server 2008) in our case.
In the Connection Manager Editor, by default the Provider is set to .Net Providers\SqlClient Data Provider
Use your test server name or IP for Server Name and under connect to database, select the database to use. I’m using ssistest.
Similarly, you need to create connection manager configuration for Destination (i.e. MySQL)
Right Click, under Connection Manager’s tab and Select ADO.NET Connection as mentioned above and click New on Connection Manager Editor.
For Provider click on the drop down arrow to select .Net Providers\odbc data Provider.
In the previous article we had created System DSN name MySQL. We will use the same here for Use user or system data source name.
Enter your login information and Test Connection. It should succeed, and then click Ok.
Now, on Configure ADO.NET Connection Manager screen, you can see both the source and destination are configured.
It’s now time to add control flows to the package, ensure you are on Control Flow tab and Drag Data Flow Task from the Control Flow Items under ToolBox.
Now click on the Data Flow tab above and Drag ADO NET Source and ADO NET Destination as shown below. You can rename the Source and Destination Names from Properties.
Right click on ADO NET SOURCE i.e. MS SQL Server 2008 and click EDIT. Make the below changes as mentioned on the screen.
As mentioned before I have ssistest database which has few sample tables, that I have exported from AdventureWorks database. I’m selecting one of the tables named HumanResources.Department for this example.
In the above screen you can see the test server selected with database and table to transfer the data from.
Similarly right click on MySQL (Destination) and click Edit. On this screen, under ADO.NET Connection Manager select MySQL.root from the drop downlist.
Since we do not have any destination table to map with the source, we need to click on New. It will show you the below message, click OK
Now remove the quotes and Click OK to create a table on destination as shown.
It will come back to the editor,
Click on the Mappings tab and check if all the columns are mapped and click OK.
This will complete the data flow design of our package.
Saving the SSIS Package
___________________________________________________
Once the package is created, save the copy of it to SSIS Package store or MSDB. Follow the below steps,
Click on File> Save Copy of Package.dtsx As from the menu it will open a window,
Select SSIS Package Store as the Package Location and Select the name of your test server.
Enter /File System/Export2MySQL as the package path and Click OK.
Select File > Save Copy of Package.dtsx as again from the menu and Select SQL Server as the Package Location. Select the name of your test server and fill in your authentication information.
Enter Export2MySQL as the package path and Click OK.
Once the copies are saved you can see them under Integration Services Stored Packages as shown below.
Changes on MySQL
___________________________________________________
Before running the Package, make this change on the MySQL Server
mysql> SET GLOBAL sql_mode= ‘ANSI’
Executing the Package
___________________________________________________
You can execute the package either from SSMS and BI Development Studio. I’m doing it from the BI, on the right hand side your BI screen you will find solution explorer, which has the Package.dtsx listed. Right click and select Execute Package.
The moment you click execute the debugging process start and the Output is show below on the BI screen. If the Data Flow Task is changed to Green, it means the package has succeeded, and if it is Red it means somewhere in the flow there was an error.
On the Data Flow Tab, you can see the number of rows transferred in the execution.
There is a progress tab on the screen, where you can check the steps taken in the flow, it also list errors and warnings if any.
Query the MySQL Server to see if the data transfer was successful as show below.
This was about transferring data from MS SQL Server 2008 to MySQL, you can do vice verse by changing the source and destination while designing the package on Data Flow tab.
Hope this article is useful.
Database Administrators Knowledge Blog 
SQL Server Integration Services 2008(SSIS) and MySQL « DbRunas – Noticias y Recursos sobre Bases de Datos
Jul 23, 2010 @ 13:35:05
Jul 23, 2010 @ 19:10:36
Hi!
just wondering, do you have to go through all these steps again for each separate table?
Jul 26, 2010 @ 07:33:30
Hi Ronald,
In case you want many tables involved in data transfer, you can add multiple Data Flow Task to the same package. This way you can also easily manage the package.
-Thanks
May 02, 2012 @ 06:31:24
I am having 300 tables to transfer how can i do it?
by creating 300 Data Flow Task to the same package?
I am new to ssis
Thanks in advance
Jul 26, 2010 @ 08:01:30
Hi! thanks for the reply 🙂
In Kettle (a.k.a. Pentaho Data Integration), you need to set it up for each table too, but fortunately it includes a wizard that can add all these steps for an entire database (or a selection of tables from a database)
Another thing that seems slightly easier there is that you do not have to explicitly define the field mapping if you don’t want to – when no special measures are taken it simply copies all fields.
kind regards,
ROland.
Jul 26, 2010 @ 09:30:44
Hi Ronald,
I have not used external tools, however the internal Import/Export wizard also creates a SSIS Package for multiple tables.
The only problem would be, you cannot transfer data for single table from that package, every time it is executed, it transfer data for the complete selection.
I would certainly give it a try by not defining explicit field mappings.
-Thanks
Aug 05, 2010 @ 15:58:20
Thank you so much for this helpful walkthrough!!
Sep 13, 2010 @ 20:49:29
at the point you say
“Now click on the Data Flow tab above and Drag ADO NET Source and ADO NET Destination as shown below. You can rename the Source and Destination Names from Properties.”
I’m looking for them, But there’s no ADO anything in my tool box. Did I miss something at the install?
Thanks
Sep 13, 2010 @ 22:08:30
Hi Everett,
You need to click on the Data flow tab on the screen, on Package Design screen. Once you click it the toolbox options will change. You will find the ADO NET Source. Please let me know if you still face any issue.
-Thanks,
Anirudh Tamsekar
Sep 14, 2010 @ 14:27:01
Under “Data Flow Sources” I have
Pointer
DataReader Source
Flat File Source
OLE DB Source
Raw File Source
XML Source
That’s all. I was thinking it had something to do with the type of Connections that were in the Connection Manager, so I rebuilt them. Still no ADO in the tool box.
I’ve done the “reset”
I’ve clicked on tools to “Choose toolbox items” and chosen everything ADO related under all tabs, but they just show up “Greyed out” in the tool box and even then the ADO NET Source or ADO NET Destination are still missing. It’s beginning to look like an install issue?
Sep 15, 2010 @ 10:55:30
Hi Everett,
What type of Installation of MS SQL (Dev or Express) do you have? Is BI installed with it?
-Thanks,
Anirudh Tamsekar
Nov 09, 2010 @ 15:27:03
I am doing exactly as shown in the tutorial but I get the following error
[ADO NET Destination [16]] Error: “An exception occurred while inserting data, the message returned by the supplier is: ERROR [42000] [MySQL] [ODBC 5.1 Driver] [mysqld-5.0. 51b-community-nt] You Have an error in your SQL syntax; Check the manual That corresponds to your MySQL server version for The Right syntax to use near ‘USR_NAME “,” USR_PASSWD “,” USR_GROUP “,” USR_LANGUE “” USR_DTLAST “,” USR_RIGHTS “‘at line 1
[SSIS.Pipeline] Error: “Error code DTS_E_PROCESSINPUTFAILED SSIS. ProcessInput component “ADO NET Destination” (16) failed with error code 0xC020844B while processing input “ADO NET Destination Input” (19). The identified component returned an error ProcessInput. This error is specific to the component, is fatal and will cause the task of stopping the flow of data. Error messages can be sent in advance with information indicating the reason for the failure.
Is it possible to insert the data from the database sql server in mysql database every time there is a change in the database sql server 2008
thanks
Nov 10, 2010 @ 07:51:13
Hi,
You can write a trigger to call SSIS package, whenever a change/update happens to your MS SQL 2008 server.
The trigger can be written in .NET to call the package and also there are several other ways to call the package, when updates occur.
-Thanks
Nov 10, 2010 @ 08:13:03
Thank you for the reply.
I also thought the trigger.
is that possible given me documentation that will help me do the trigger
thankssssssss
Nov 10, 2010 @ 09:26:39
I solved the problem in SSIS.
I still have the problem of creating the trigger.
therefore the solution of the error SSIS is to execute this line in mysql:
SET GLOBAL sql_mode=’ansi’;
Nov 10, 2010 @ 19:16:59
Hi,
I will try and send you the document. 🙂
-Thanks
Nov 11, 2010 @ 08:25:49
OHH thanks
Nov 24, 2010 @ 12:36:26
HI,
I try to run the package with SQL Server Agent.
but I get errors every time it’s a different error.
what I absolutely have to check?.
such as authorizations, proxy
thanks
Jan 04, 2011 @ 08:41:04
Hi,
Am looking to implement the automatic update of the mysql database whenever a transaction happens on the MS Server and I think the trigger option could work for me as well. Could you help me with the documentation on how I can implement this?
Kind Regards,
Mwamba.
Jan 14, 2011 @ 12:30:53
Hi,
Don t know how can we solve this pb :
[Table options To MySQL Arn [62]] Error: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.36-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”toto” (“titi”, “tata”) VALUES (1, ‘url’)’ at line 1
Thx a lot for your help
Jan 20, 2011 @ 06:35:55
Thanx. Great post !
Mar 12, 2011 @ 00:06:25
Thanks for this great post… very helpful.
Re: ADO.NET not showing up in DataFlow Sources/Destinations:
I installed the Cherry City Software MySQL OLE DB provider (see https://cherrycitysoftware.com/CCS/Providers/ProvMySQL.aspx) and was thus able to create an OLE DB connection to my MySQL db. This completely eliminates the ADO.NET problem.
Apr 30, 2011 @ 12:56:34
Death!
Thanks for post!!! This is the best solution (a detailed one) on the Internet.
Vivi
Jun 03, 2011 @ 19:05:22
I followed the instructions and I can get the package to run from BIDS, but can not successfully run as a SQL agent job. I can start the job and it runs fine some of the time. I get the following errors:
1. Description: ADO NET Destination has failed to acquire the connection
and sometimes the next error:
2. Failed to acquire connection “mysqli01_test_connection”. Connection may not be configured correctly or you may not have the right permissions on this connection.
I have checked that the account has the correct permissions. Any ideas?
Jun 16, 2011 @ 00:13:16
On my source ADO Net, I seem to be getting the list of tables but I can’t close the dialog box or view columns. permission issue on the table perhaps?
error as follows:
TITLE: Microsoft Visual Studio
——————————
There was an error displaying the preview.
——————————
ADDITIONAL INFORMATION:
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.57-enterprise-commercial-advanced-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”City”‘ at line 1 (myodbc5.dll)
——————————
BUTTONS:
OK
——————————
Jun 17, 2011 @ 18:29:23
great tutorial, helped me very congratulations.
Sep 26, 2011 @ 13:49:14
Hi,
am new to SSIS, please how can i develop an SSIS package that checks for new records (automatically run) in one Ms SQL 2008 table and pulls them if any exist and save into another Ms Sql 2008 Database table.
Oct 03, 2011 @ 08:46:36
Say I want to migrate data from a table on one server (connection) to multiple tables on another server. Can you give some guidelines on how to do that
Oct 17, 2011 @ 02:24:49
Thanks for putting up the great post, I’ve run into a problem trying to follow the directions here. When executing the package I get a failure on the mysql side. the failure has the following error message “Unknown column ‘p1’ in ‘field list'”
By enabling the general query log on mysql I was able to confirm that SSIS is indeed trying to insert p1 through p25 as the values for the columns in my table. I’ve tried using both the mysql .net provider and the ODBC .net provider.
Any help would be greatly appreciated
Oct 18, 2011 @ 15:54:47
Fantastic post, i’ve got it working in my enviroment up to the point where the package runs and i get an error “Unknown column ‘p1’ in ‘field list'” – needless to say there is no p1 in the list.
Any suggestions as to where to look, my google searching is so far in vain, I found a few posts that have redirected me back here!
Feb 02, 2012 @ 12:20:15
What about doing it the other way around.. getting data from MySQL to MSSQL 2008
Feb 26, 2012 @ 06:01:53
Hi,
Awesome stuff thank you!
This works so well for my integration on a local machine. However, how would i go ahead to transfer data from SQL Server (local) to MySQL (online)?
Regards,
W
Mar 09, 2012 @ 20:11:18
Hi,
Can you help me with this problem pls:
Error: 0xC020844B at Data Flow Task, ADO NET Destination [16]: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.61-2~dotdeb.0-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”faktury” (“cislo”) VALUES (‘ADM ‘)’ at line 1
May 02, 2012 @ 10:27:16
Works fine. Thanks alot. this helps me very much to solve the issue. Thanks again.
May 17, 2012 @ 01:26:25
Hi, I have a situation in wich the data transfer from MySQL server to SQL2008 using SSIS is taking too much time. I am transfering around 40k records, If I do it from a python script it takes 5 seconds @ around 20MB transfer rate, but if I do it from SSIS it takes aroung 40 seconds @ around 1.5MB transfer rate.
I have tryied to update connector, odbc driver, etc etc… but no luck…. Can you please point me to the right direction?
Feb 03, 2013 @ 22:50:34
While creating the connection manager for ODBC data provider, I am unable to select the data source name. It is blank. I clicked refresh and still same. can you help please?
Feb 17, 2013 @ 09:35:49
I followed all the steps listed above.
But I am getting the error as ” Could not retrieve the table information for the connection manager ‘****’. Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Additional Information:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.data)”
I am getting the above error when trying to select/create new table on the destination server(Mysql) from SSIS package.
>> I am using windows server 2003 64 bit.
Apr 26, 2013 @ 10:16:17
Hi, Can it be run as an automated service? For example, I want to transfer data on a regular interval say 1 hr.
Apr 27, 2015 @ 07:27:09
Hi i am transfering data from SQL server 2008 to MYSQl using odbc conector and ado.net destination. But transfer rate is very slow. i.e for 1 lack records it will taking 3 hours to transfer. How can we improve the performance. Can anybody suggest on this?
Jun 25, 2015 @ 16:16:21
This is the perfect web site for everyone who wants to understand this topic. You realize a whole lot its almost tough to argue with you (not that I personally will need to…HaHa). You certainly put a brand new spin on a subject that has been written about for decades. Great stuff, just wonderful!