none
SSIS ETL from mysql to mssql

    Pregunta

  • 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

    martes, 06 de marzo de 2012 13:32

Respuestas

Todas las respuestas

  • 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

    martes, 06 de marzo de 2012 13:38
  • 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

    • Propuesto como respuesta jandho martes, 06 de marzo de 2012 14:15
    martes, 06 de marzo de 2012 14:14
  • 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/

    martes, 06 de marzo de 2012 18:05
  • @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

    miércoles, 07 de marzo de 2012 9:45
  • 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

    miércoles, 07 de marzo de 2012 9:47
  • @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

    miércoles, 07 de marzo de 2012 9:50
  • 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

    miércoles, 07 de marzo de 2012 15:19
  • @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

    miércoles, 07 de marzo de 2012 15:31
  • @Jan

    Sure! Thanks a lot!


    Best Regards Ingo Hoefker MCTS 2008 MCITP 2008


    jueves, 08 de marzo de 2012 7:39
  • 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

    jueves, 08 de marzo de 2012 7:43
  • 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

    viernes, 09 de marzo de 2012 11:26
  • 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

    lunes, 12 de marzo de 2012 7:15
  • 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
    • Marcado como respuesta Ingo Hoefker miércoles, 14 de marzo de 2012 10:23
    martes, 13 de marzo de 2012 7:43
  • 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


    • Editado Addie123 martes, 13 de marzo de 2012 9:49
    • Marcado como respuesta Ingo Hoefker miércoles, 14 de marzo de 2012 10:23
    martes, 13 de marzo de 2012 9:43
  • 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

    miércoles, 14 de marzo de 2012 10:22