Post reply

Data flow task from SQL to MySQL

  • June 10, 2009 at 9:47 am

    #138593

    I'm trying to move data from SQL into a MySQL database. I think I need an OLEDB provider. I tried the one from Cherry City Software but keep getting oledb errors.

    Anyone else done this or have any ideas or options.

    Thanks

    Allen

  • June 10, 2009 at 1:03 pm

    #1007636

    Have a look at the following and see fi they are of any help...

    http://blogs.msdn.com/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

    http://blogs.msdn.com/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx

    http://blogs.msdn.com/mattm/archive/2009/03/02/how-do-i-do-update-and-delete-if-i-don-t-have-an-oledb-provider.aspx

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • June 11, 2009 at 5:23 am

    #1007915

    Thanks Luke,

    I tried the 'bulid a string and execute it in a loop' method and although I got no errors nothing was written to the MySQL table. I tried this using an ODBC connection and also a OLE DB connection but with no success.

    My gut feeling is that its something to do with the connector/provider. When I try to use a Data Flow task I get messages that the destination columns are read-only (I'm connected as root)!

  • June 11, 2009 at 5:49 am

    #1007927

    I'm not a MySQL expert, as I've mostly dealt with PostgreSQL on the open source front, but any chance you have it locked down by IP or something? I remember in Postgres I had the ability to provide different levels of access not only by user, but also based on IP. Additionally, I'm guessing you're trying to connect with Root for troubleshooting purposes, but I'd suggest using a limited user in production. Perhaps connecting as root is part of the problem? Standard security practices at least in most of the open source shops I've worked in dictated that we set root to not be allowed to log in directly except from the local console. Everything else we had to log in as ourselves and SU. Perhaps that could be causing some of your issues?

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • June 11, 2009 at 6:38 am

    #1007962

    Thanks Luke - I got there. I had left some bad options set in my Execute SQL task and of course I was sure I hadn't!

  • June 11, 2009 at 6:47 am

    #1007966

    Yup, that happens, particularly when it seems you've been banging your head against a wall for a while on the same issue. You end up "knowing" you already did something or put something back when you actually didn't.

    Glad you got it sorted.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply