none
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

    Tuesday, March 06, 2012 1:32 PM

Answers

All replies

  • 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

    Tuesday, March 06, 2012 1:38 PM
  • 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

    • Proposed as answer by jandho Tuesday, March 06, 2012 2:15 PM
    Tuesday, March 06, 2012 2:14 PM
  • 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/

    Tuesday, March 06, 2012 6:05 PM
  • @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

    Wednesday, March 07, 2012 9:45 AM
  • 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

    Wednesday, March 07, 2012 9:47 AM
  • @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

    Wednesday, March 07, 2012 9:50 AM
  • 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

    Wednesday, March 07, 2012 3:19 PM
  • @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

    Wednesday, March 07, 2012 3:31 PM
  • @Jan

    Sure! Thanks a lot!


    Best Regards Ingo Hoefker MCTS 2008 MCITP 2008


    Thursday, March 08, 2012 7:39 AM
  • 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

    Thursday, March 08, 2012 7:43 AM
  • 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

    Friday, March 09, 2012 11:26 AM
  • 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

    Monday, March 12, 2012 7:15 AM
  • 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
    • Marked as answer by Ingo Hoefker Wednesday, March 14, 2012 10:23 AM
    Tuesday, March 13, 2012 7:43 AM
  • 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


    • Edited by Addie123 Tuesday, March 13, 2012 9:49 AM
    • Marked as answer by Ingo Hoefker Wednesday, March 14, 2012 10:23 AM
    Tuesday, March 13, 2012 9:43 AM
  • 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

    Wednesday, March 14, 2012 10:22 AM