SSIS和MySQL-表名称分隔符问题 [英] SSIS and MySQL - Table Name Delimiter Issue

问题描述

我正在尝试使用SQL Server 2008 SSIS将行从Access数据库插入MySQL数据库中.

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

问题出在定界符上.我正在使用5.1 ODBC驱动程序，并且可以连接到MySql并从ADO.Net目标数据源中选择一个表. MySql表在SSIS包编辑器中均以双引号分隔:

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"

如果表名中有空格，则无法从ADO.NET目标编辑器上的使用表或视图"文本框中删除双引号或将其替换为其他内容. 当SSIS将Insert查询放在一起时，它将保留双引号并添加单引号.

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).

我似乎无法控制这种行为.有什么建议?我可以手动编码SQL的其他程序包类型没有此问题.

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.

推荐答案

对不起，InnerJoin，我不得不把被接受的答案从您身边拿走.我发现一种解决方法:

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

解决方案是使用执行以下操作的Execute Sql任务，将连接重用于所有任务，并在执行任何插入操作之前为连接打开ANSI引号.

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' 

这篇关于SSIS和MySQL-表名称分隔符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！