Redefine DELIMITER in MySQL script not supported in SSIS client

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.