1

Here I am, day three... attempting to sync a data view on a Windows Vista box (64 bit) running MSSQL 2012 and Visual Studio 2010. Sanity is slipping and hunger for progress fills my attention.

I went through hell trying to get the MySQL ODBC drivers to get the job but to no avail...everyone seems to be lost and all the threads I can find are solutions that do not work for me. The problem: System DSN's not being seen by SSIS.

SSIS DSN Not Showing as ODBC Data Source

I make the decision to try out the ADO.NET connector...and to my surprise it is actually in the selection list in data sources in SSIS. So I take off running to create a Data Flow Task, create an ADO.NET Source (a local MSSQL DB)...all is good as usual.

Then I move swiftly to creating a ADO.NET Destination, enter my credentials...wow, I am selecting a database finally on my linux server! Happy thinking that I finally have figured a way to get the job done.

Then I move to mappings...nope, something is wrong...I am getting an error that hurts my eyes:

Pipeline component has returned HRESULT error code 0xC0208457 from a a method call. Error at Data Flow Task [ADO NET Destination [81]]: Failed to get properties of external columns. The table name you entered may not exist or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are" 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 "database".tablename" at line 1.

The descriptor files on path C:\Program Files (x86)\Microsoft SQL Server\110\DTS\ProviderDescriptors\ does not contain schema information for connection of type MySQL.Data.MySqlClient.MySqlConnection.

So it looks like it can't the information and therefore I cannot map the tables properly.

Any ideas on this would be ultra helpful...thanks in advance to All!

Improve this question

2 Answers 2

Reset to default
1

Ok well I found the problem...by default MS puts the MySQL db and table name in quotes. I remove the quotes and I can finally map the tables together. Yet another problem though...for another post.

Improve this answer
2
  • How did you change the default behavour to not put the table name in quotes?
    – user131283
    Aug 7, 2012 at 16:21
  • Just delete them. Do you not have that option. Would be lovely to know if you accomplish this, and how....I gave up and moved on with my life ignoring its existence until I have found a better way.
    – user1114330
    Aug 7, 2012 at 20:36
-1

By default MSSQL puts the columns and table names in quotes, for MySQL we need to add ANSI_QUOTES to sql-mode.

It can be set in my.ini or my.cnf depending on your Operating System.

For Windows server 2012 MySQL 5.6, for example, you can find it in C:\ProgramData\MySQL\MySQL Server 5.6.

In my.ini, you need to find #sql-mode and update it, adding 'ANSI_QUOTES' to the end. An example is:

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"

Then you need to restart MySQL service, and go next in the MSSQL's SSIS pannel.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .