SSIS and MySQL - Table Name Delimiter Issue

Question

I am trying to insert rows into a MySQL database from an Access database using SQL Server 2008 SSIS.

TITLE: Microsoft SQL Server Management Studio
------------------------------

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51a-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 '"orders"' at line 1

The problem is with the delimiters. I am using the 5.1 ODBC driver, and I can connect to MySql and select a table from the ADO.Net destination data source. The MySql tables all show up delimited with double-quotes in the SSIS package editor:

"shipto addresses"

Removing the double quotes from the "Use a table or view" text box on the ADO.NET Destination Editor or replacing them with something else does not work if there is a space in the table name. When SSIS puts the Insert query together, it retains the double quotes and adds single quotes.

The error above is shown when I click on "Preview" in the editor, and a similar error is thrown when I run the package (albeit then from the actual insert statement).

I don't seem to have control over this behavior. Any suggestions? Other package types where I can hand-code the SQL don't have this problem.

Answer 1

Try using square brackets around the table names. That may help.

EDIT: If you can, I would create views (with no spaces) based on the Access tables, and use those to export. Even if it means building another Access database with linked tables, I think this is your best bet.

Answer 2

I've always struggled with using SSIS with MYSQL directly. Even after installing the ODBC drivers, they just don't play well in data flows. I've always ended up creating linked ODBC connections between SQL Server and MYSQL. I then rely on linked server queries to bring over data. Instead of using a SSIS data flow task, I use an Execute SQL command, usually in the form of a stored procedure that executes an OPENQUERY.

One solution you could do is load the data into a SQL Server database and use it as a staging environment before you load it into the MYSQL database. I regularly move data between SQL Server 2008 and MYSQL and in the past I use to regularly move data between Access and SQL Server.

Another possible solution is to transform the incoming Access data before it loads into the MYSQL database. That may give you a chance to clean up the column names and the actual data that's going through to MYSQL.

Let me know if either of these work for you.

Answer 3

Sorry InnerJoin, I had to take the accepted answer away from you. I found a workaround here:

The solution is to reuse the connection for all tasks, and to turn ANSI quotes on for the connection before you do any inserts, with an Execute Sql task that runs the following:

set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
     NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'