MySQL Data Transfer using Sql Server Integration Services (SSIS)

Exclusive offer: get 50% off this eBook here
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 — Save 50%

An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.

$23.99    $12.00
by Jayaram Krishnaswamy | August 2009 | Microsoft PHP

There are a large number of posts on various difficulties experienced while transferring data from MySQL using Microsoft SQL Server Integration Services. While the transfer of data from MySQL to Microsoft SQL Server 2008 is not fraught with any blocking issues, transfer of data from SQL Server 2008 to MySQL has presented various problems. There are some workarounds suggested. In this article by Dr. Jay Krishnaswamy, data transfer to MySQL using SQL Server Integration Services will be described.

(For more resources on Microsoft, see here.)

If you are new to SQL Server Integration Services (SSIS) you may want to read a book by the same author on Beginners Guide to SQL Server Integration Services Using Visual Studio 2005, published by Packt.

Connectivity with MySQL

For data interchange with MySQL there are two options one of which can be accessed in the connection wizards of SQL Server Integration Services assuming you have installed the programs. The other can be used to set up a ODBC DSN as described further down. The two connection options are:

In this article we will be using the ODBC connector for MySQL which can be downloaded from the MySQL Site. The connector will be used to create an ODBC DSN.

Transferring a table from SQL Server 2008 to MySQL

We will transfer a table in the TestNorthwind database on SQL Server 2008 (Enterprise & Evaluation) to MySQL server database. The MySQL database we are using is described in the article on Exporting data from MS Access 2003 to MySQL. In another article, MySQL Linked Server on SQL Server 2008, creating an ODBC DSN for MySQL was described. We will be using the DSN created in that article.

Creating an Integration Services project in Visual Studio 2008

Start the Visual Studio 2008 program from its shortcut. Click File | New | Project... to open the New Project window and select an integration services template from the business intelligence projects by providing a suitable name. The project folder will have a file called Package.dtsx which can be renamed with a custom name.

Add and configure an ADO.NET Source

The Project's package designer will be open displaying the Control Flow tab. Drag and drop a Data Flow Task on to the control flow tabbed page. Click next on the Data Flow tab in the designer to display the Data Flow page. Read the instructions on this page. Drag and drop a ADO.NET Source from the Data Flow Sources items in the Toolbox.

It is assumed that you can set up a connection manager to the resident SQL Server 2008 on your machine. The next figure shows the configured connection manager to the SQL Server 2008. The table (PrincetonTemp) that will be transferred is in the TestNorthwind database. The authentication is Windows and a .NET provider is used to access the data. You may also test the connection by clicking the Test Connection button. If you are not sure of this you can review the free chapter from the book available here.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

If the connection shown above is correctly configured, the test should indicate a successful connection. Right click the ADO.NET source and from the drop-down click Edit. The ADO.NET Source Editor gets displayed. As mentioned earlier you should be able to access the table and view objects on the database as shown in the next figure.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

We have chosen to transfer a simple table, PrincetonTemp from the TestNorthwind database on SQL Server 2008. It has a only couple of columns as shown in the Columns page of the ADO.NET Source Editor.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

The default for the Error page setting has been assumed, that is, if there is an error or truncation of data the task will fail.

Add an ADO.NET destination and port the data from the source

Drag and drop an ADO.NET destination item from under Data Flow Destinations items in the Toolbox on to the data flow page of the designer. There are two ways to arrange for the data to flow from source to the destination. The easy way is just drag the green dangling line from the source with your mouse and let go on the ADO.NET destination. A solid line will connect the source and the destination as shown.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

(For more resources on Microsoft, see here.)

Configure a connection manager to connect to MySQL

In the Connection Manager's pane under the Package designer right click to display a pop-up menu which allows you to make a new connection. When you agree to make a new ADO.NET Connection the Configure ADO.NET connection Manager's window shows up and click on New... button on this page.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

The connection manager's page gets displayed as shown. In the Providers drop-down you will see a number of providers. There are the two providers that you can use, the ODBC through the connector and the MySQL Data Provider.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Click on the Odbc Data Provider. As mentioned previously we will be using the System DSN MySQL_Link created earlier for the other article shown in the drop-down list of available ODBC DSN's.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Provide the USERID and Password; click the Test Connection button. If all the information is correct you should get a success message as shown.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Close out of the message as well as the Configure ADO.NET Connection Manager windows. Right click the ADO.NET Destination to display its editor window. In the drop-down for connection manager you should be able to pick the connection Manager you created in the previous step (MySQL_INK.root) as shown.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Click on the New... button to create a Table or View. You will get a warning message regarding not knowing the mapping to SSIS as shown.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Click OK. The create table window gets displayed as shown. Notice that the table is displaying all the columns from the table that the source is sending out.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

If you were to click OK, you would get an error that the syntax is not correct as shown.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Modify the table as shown to change the destination table name (your choice) and the data type.

CREATE TABLE From2k8(
"Id" INT,
"Month" VARCHAR(10),
"Temperature" DOUBLE PRECISION,
"RecordHigh" DOUBLE PRECISION
)

Click OK. Again you get the same error regarding syntax not being correct. Modify the Create Table statement further as shown.

CREATE TABLE From2k8 (
Id INT,
Month VARCHAR(10),
Temperature DOUBLE PRECISION,
RecordHigh DOUBLE PRECISION
)

Click OK after the above modification. The table gets added to the ADO.NET Destination Manager Editor as shown.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Click on the Mappings on the left side of the ADO.NET Destination Editor. The column mappings page gets displayed as shown. We accept the default settings for Error Output page.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Click OK. Build the project and execute the package by right clicking the package and choosing Execute Package. The program runs and processes the package and ends up being unsuccessful with the error message in the Progress tab of the project as shown (only relevant message is shown here).

....
.....
[SSIS.Pipeline] Information: Execute phase is beginning.
[ADO NET Destination 1 [165]] 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.30-community]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 '"Id",
"Month", "Temperature", "RecordHigh") VALUES (1, 'Jan ', 4.000000000' at line 1
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput
method on component "ADO NET Destination 1" (165) failed with error code 0xC020844B
while processing input "ADO NET Destination Input" (168). The identified component
returned an error from the ProcessInput method. The error is specific to the component,
but the error is fatal and will cause the Data Flow task to stop running. There may
be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Information: Post Execute phase is beginning.
......
....
Task Data Flow Task failed
....

Start the MySQL Server and login to it. Run the following commands as shown in the next figure. By setting the mode to 'ANSI' makes the syntax more standard like as MySQL can cater to clients using other SQL modes. This is why the above error is returned although the syntax itself appears correct. In fact a create statement run on command line to create a table directly on MySQL could not create a table and returned an error when SSIS was used to create the same table.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

    

After running the above statements, build the BI project and execute the package. This time the execution is will be successful and you can query the MySQL Server as in the following:

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Summary

The article describes step by step transferring a table from SQL Server 2008 to MySQL using ODBC connectivity. For successful transfer the data type differences between SQL Server 2008 and the MySQL version must be properly taken into consideration as well as correctly setting the SQL_Mode property of MySQL Server.


Further resources on this subject:


Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform


Your rating: None Average: 5 (2 votes)
SSIS is usefull for data use & abuse by
you can use this for good or bad purpose. so,keep your data confidential.
ssis doubts by
When i map the field of integer type of flat file source to varchar(10) colum in oledb destination , it gives warning like Truncation may occur due to inserting the data from data flow column with a lendgh of 50 my package is running , but it give warning , can i have hape?
thanks for the help by
With all the posts about problems wrting to MySQL with SSIS - this is the only info that actually worked, Thanks

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
e
y
u
7
S
h
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software