SSIS ETL from mysql to mssql

Question

Hi, I know there are already some posts about this, but I did not find a solution for my problem.

My source is on a mysql db, I installed the mysql driver, chose the ado net source, connected it to an ole db connection and everything looks nice. Preview in the source is visible.

When I press execute the process works without error. But not a single row was copied. I tried different destinations, I tried even very simple source tables but no chance.

My system is windows 7 64-bit and SQL server 2008 R2.

Any suggestions?

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 1

Hi,

If I were you, I'd start by activating SSIS logs.

Then run the process again and post your logs.

Good luck!

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

Are the mysql drivers 32-bit, are you running the process from you development studio?

In the Project Properties of an Integration Services package, you can select 32-bit or 64-bit execution by setting the value of the Run64BitRuntime property on the Debugging page. By default, the value of this property is True. When the 64-bit version of the Integration Services runtime is not installed, this setting is ignored.

http://msdn.microsoft.com/en-us/library/ms141766.aspx

---

Jan D'Hondt - Database and .NET development

Answer 3

Hi Ingo,

I would recommend you install and use MySQL ODBC drivers. In my opinion these are the best supported and complete drivers for MySQL.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 4

@Jandho

Hi Jan, I would like to try switching to 32-bit-mode.

I used the 64-bit connector and the bids is running in 64-bit-mode. Now I tried to change it and downloaded the 32-bit .net connector. Installing gives me the error message, that there was a previous version of that connector that has to be deleted first. I did not install it. I don't find it in the mysql folder or somewhere else. How can I delete it or force the installation of the newer one?

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 5

Hi Arthur, I tried the odbc connectors before but it did not work neither. Best Regards, Ingo

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 6

@Sebastian

Hey Sebastian, thanks for your help. The problem is that there is no error. Everything looks nice and there is no error message. In the log is nothing but successful processing listed. The only wrong thing is there is not a single row in the destination.

Best Regards, Ingo

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 7

Hi Ingo,

Today I do not have access to the server on which I developed this. I will be able to check tomorrow and keep you posted. In case you would no longer need this info, let me know.

---

Jan D'Hondt - Database and .NET development

Answer 8

@Sebastian

Hey Sebastian, thanks for your help. The problem is that there is no error. Everything looks nice and there is no error message. In the log is nothing but successful processing listed. The only wrong thing is there is not a single row in the destination.

Best Regards, Ingo

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Ingo

Which MySql .net Connector version you are using? If it is less than 6.0 try to install a newer version like 6.4 and run the package again.

Regards

Harris

Answer 9

@Jan

Sure! Thanks a lot!

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 10

Hi Harris, I tried that (see post before). I switched to 32-bit-mode so that I can use the 6.4.4 connector. But when I try to install it there is the error message that an earlier version needs to be deleted first. I saw that problem in some other posts but no solution for it. Do you have any idea? I don't know where this earlier version could be found to delete it. It is no in the mysql folder where the connectors are usually.

Thanks for you help!

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 11

meanwhile, due to other ICT problems, I was unable to visit the place where the MYSQL connection is used. Blame it on the recent solar activity making servers unstable. When I get myhands on that server I will try to post you the info.

---

Jan D'Hondt - Database and .NET development

Answer 12

meanwhile, due to other ICT problems, I was unable to visit the place where the MYSQL connection is used. Blame it on the recent solar activity making servers unstable. When I get myhands on that server I will try to post you the info.

---

Jan D'Hondt - Database and .NET development

OK! Thanks!

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008

Answer 13

Hi Ingo Hoefker,

Please refer to the following article about Integrate MySQL and SQL Server:
http://www.sqlmag.com/article/business-intelligence-development-studio-bids/integrate-mysql-and-sql-server 

Thanks,
Eileen

Answer 14

Hi Ingo,

After youve installed the MYSQL connector add the MYSQL server in the server explorer in bids

(view->server explorer->rt.click-.add connection) you'd notice MYSQL in the list of servers that can be added-> configure the mysql server and then use it in the data connector.....(youd now find mysql provider in the list of .netdataproviders in the connection manager

Regards

Answer 15

Hi guys, thanks a lot for your help. It works!!

See you next time. Hope I can help you one day...

---

Best Regards Ingo Hoefker MCTS 2008 MCITP 2008