[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1493
  • Last Modified:

Transfer data from MSSQL 2008 to MySQL using SSIS package

I am trying to write an SSIS package to transfer data from SQL Server 2008 to MySQL.

I am trying to directly transfer the data between the tables...

The following is the error I am getting:

[ADO NET Destination [89]] Error: An exception has occurred during data insertion, the message returned from the provider is: 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 '"state_id", "state_name", "state_code", "state_system") VALUES (p1, p2, p3, p4);' at line 1

I am not using any query to transfer the data. But I get the above message. I don't understand how to fix this.

There is no issue when I transfer the data between SQL 2005 and MySQL.  

Please help.

  • 5
  • 2
2 Solutions
Kevin CrossChief Technology OfficerCommented:

MySQL is probably not set for ANSI_QUOTES, so is not recognizing "state_id" as a column name, for example. http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
If you do this manually, you are probably either leaving off the " or using `state_id` which is good for MySQL.

Hope that helps!
spraocsAuthor Commented:
Thanks. But, I have a question.
How do I set ANSI_QUOTES more for MySQL in SQL Server SSIS?

I am like a beginner in both MS SQL and MySQL..

Kevin CrossChief Technology OfficerCommented:
You set that sql-mode for MySQL. Here is maybe a better reference from the MySQL manual:

If you have MySQL Workbench, then you would open your server under Server Administration. Once in the Admin, you will see a Configuration tab, you can add ANSI_QUOTES to the end of your current sql-mode string. You will then go back to startup tab and stop MySQL, then subsequently start it.

Alternatively, you would find the my.ini or my.cnf depending on your Operating System. For Windows 7 MySQL 5.5, for example, C:\Program Files\MySQL\MySQL Server 5.5. In the .ini, you need to find #sql-mode and update it. An example is:

# Set the SQL mode to strict

Notice, multiple modes can be set at a time, separated by a comma and the entire thing in quotes. Restart the MySQL service after changing/adding that configuration value.

Hope that helps!

P.S. I tested and works for sure in SSIS. I cannot find a way to enable it via SSIS directly. Although, I did find this setting which by its help explanation appeared to be what was wanted, but did not work for me. My test was very basic and using Import/Export package type, so may work for you. This is on the .NET Provider for MySQL.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Kevin CrossChief Technology OfficerCommented:
Okay, I confirmed. That setting may come in handy, but it is for enabling [column name] and not "column name". http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html
I guess something that may work is disabling ANSI quotes in SQL Server, which may force it to use [column name] for generated DDL/DML scripts and so may work. Again, give it a try. I set that setting after I had created the package; therefore, you can try to see what happens if you set that in the source/destination setup before the wizard generates any of the scripts and see if that actually fixes the problem without using ANSI_QUOTES on MySQL side. (I may actually test again on my end also.)

Good luck!
Kevin CrossChief Technology OfficerCommented:
Verified that setting Sql Server Mode did not stop SSIS from creating the scripts as "Table"; however, once created, I can manually edit to [Table] and successfully passes to MySQL without having ANSI_QUOTES on there. If I knew how to set SSIS to default to [Table] and [Column] versus "Object" (i.e., ANSI quoted identifier), then that would be another way around. I have seen this done using ODBC driver. I tend to use native driver or OLEDB for SQL and neither have a way to set ANSI quotes off. When using ODBC, however, you will see "Use ANSI quoted identifiers" as an option that can be unchecked. But all the while doing this, there is a side of me asking WHY as ANSI-compliant code is the way to go. :)
Reza RadCommented:
there are some serious problems in ADO.NET destination with .Net or ODBC connectors with MySQL.
I tried many times to overcome this issue, but always there were some  problems.
I recommend to work with Execute SQL Task, I mean load whole data from sql server into a recordsSet Destination into an Object type variable.
and then in the control flow loops through this variable with foreach loop container, and finally use an Execute SQL Task inside the foreach loop to insert each records one by one.
I wrote an example ( about UPSERT scenario with MYSQL in SSIS ), you can find clues on how to work in this article:

please let me know if you have problems on implementation
Kevin CrossChief Technology OfficerCommented:
reza_rad: so it sounds like "manual" typing of SQL is what the SSIS pros use. :) I felt silly suggesting that, but it seemed like the only way to control it 100%, especially because of some of the syntax differences like UPSERT (i.e., INSERT ... ON DUPLICATE KEY UPDATE). Thanks for jumping on.
spraocsAuthor Commented:

thanks for the detailed information about the sql_mode. It really helped me to understand the problem and look in the right direction. I tried to set that on the server using Workbench.. But, for some reason, I could not set it. Probably because I didn't have enough access or something else.  I didn't dig deep into it. But I was sure of your solution about setting the sql_mode.

reza rad:

This is exactly when your post helped me, though not directly.. it was a very nice article about the UPSERT. But, I was looking for something simpler and straight forward like --- use the connection manager, use data source and destination, connect and set some parameters or do some simple changes and the data is transferred --- Your article still helped when I saw the second link at the beginning of your article

 that link has another link within it in the comments section, which really helped me for the required solution.


That was the exact thing I needed - Just using an Execute SQL and set the sql_mode fixed the issue.

Thanks a lot to both of you for the valuable solution.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now