Tuesday, October 09, 2012 5:54 AM
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?
- Changed Type ArthurZMVP, Moderator Tuesday, October 09, 2012 1:26 PM Revised to reflect the intent
Tuesday, October 09, 2012 1:29 PMModerator
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
Wednesday, October 10, 2012 6:12 AM
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.
Wednesday, October 10, 2012 1:35 PMModerator
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
Wednesday, October 10, 2012 3:28 PM
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:
create procedure abc
select count(*) from tableA;
select count(*) from tableB;
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.
Wednesday, October 10, 2012 3:37 PMModerator
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
Wednesday, October 10, 2012 5:58 PM
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.