Redefine DELIMITER in MySQL script not supported in SSIS client
Steps to reproduce: 1) create a database with one minimum one table and one stored procedure. 2) export it into dump file 3) Create flat file connection in SSIS and try importing script into MYSQL database. It says, problem in syntax near delimiter. Earlier this problem was with .NET connector, but has been fixed. It is still unsupported in SSIS client to handle redefined DELIMITER. Could somebody suggest what can be done?
October 9th, 2012 1:54am
How to export a database to a dump file? Is your source a SQL Server database? I guess you refer to scripting the objects of a given database? Where and how this was fixed? In short, to me there is no enough information to conclude on anything.Arthur My Blog
October 9th, 2012 9:29am
My source is a mysql dump file. I need to import it into Mysql database using SSIS. The points I mentioned above is just in case you don't have dump file with you. You can create Dump of Mysql database using `mysqldump` command. You can refer to the http://bugs.mysql.com/bug.php?id=46429 bug(solved) with .NET connector as well.
October 10th, 2012 2:12am
What SSIS does differently is the question. So if you generate the dump "using `mysqldump`" from SSIS does it differ anyhow from the one generated using another method? What happens if you do a diff on them? I guess you have syntactical differences at this point that fail the process. Or the issue is in importing it? Let's narrow down to where it is. Arthur My Blog
October 28th, 2012 9:13am
mysqldump is mysql utility that has nothing to do with SSIS. The issue is in importing. In the SQL script every query works but DELIMITER. Try SQL mentioned below: DELIMITER $$ create procedure abc as begin select count(*) from tableA; select count(*) from tableB; end DELIMITER ; It works on normal mysql client. But if you try importing it(Into Mysql DB) through SSIS file connection import, it fails. This problem has been recently fixed with many clients(phpadmin). Looking for the same to be fixed in SSIS as well or workaround.
October 28th, 2012 11:06am
I now see what is the problem, and I think it is a driver issue, not SSIS, can you find and use a different driver for MySQL? I guess this will automatically fix this issue. PS: I am also guessing you can simply copy and paste this code into Execute SQL Task and it would fail. In short, if your answer to the above is yes, then ask the MySQL tool or driver creator to fix that Arthur My Blog
October 28th, 2012 11:15am
Yes, you are right. Same query fails into SQL executor task as well. It depends upon how the query is parsed and send to the Mysql Server. The problem with the driver/connector has been fixed. I am using version 6.5.x and it has been fixed in version 6.3+. One can refer to the Release Notes: http://dev.mysql.com/doc/refman/5.1/en/connector-net-news-6-3-1.html So I came to conclusion, the problem is with SSIS only, which is playing the role of client here.
October 28th, 2012 1:36pm