MySql SSIS Hangs with ODBC Drivers

Hi, we are writting a SQL Server Integration Services package to import data from a MySql database to a Sql Database.

We are using ODBC 3.51 Drivers to connect to the MySql Database in SSIS. The package runs perfectly in design mode. When we schedule the package to run, the package seems to hang about 1/3 of the times.

What can this be. We used to the package and ran it from a Sql Server 2005 to a Sql Server 2005 database. This setup works perfectly. When i'm doing MySql to Sql Server 2005, 1/3 of the times, it does not work.

Can anyone help me with this?

Giovan Gentile
Ayuda Media Systems

Implementation of ODBC is different by different vendors and for each different data source (after all, that was the goal of it). Therefore because an ODBC connection works fine with a particular source database is no guarantee an ODBC connection against a different database will work for your purposes- _the implementation code is totally different_.

Also, allowing implicit data type conversions (in my experience) is often the source of problems in moving data between different RDBMS implementations. Create views in the source to explicitly make any data type conversions required for the Sql Server destination. That can only help...

Ken

|||

Hello Ken,

Importing from mySQL with the 3.51 works fine for me.

Does your package "hangs" on big resulsets specifically ? If so, it could be that your datasource in configured to cache results from mySQL before making them available to the consumer. That's the default behavior.

Turning on "Allow big results" and "Don't cache result (forward cursor only)" options at the ODBC datasource level fixed the pb for me.

Hope it helps.

--

Jerome

MySQL OLEDB Driver

Hi!

I have a small question, there are OLEDB drivers availible for .NET on the MYSQL download site. But when I install them I'm not able to see them inside SQLIS, I've tried them inside Visual Studio and they work fine.

Is there a way to get them "registred" or add them as a reference inside SQLIS so that I may choose them when setting up an OLEDB Source / Destination?For performance reasons, the Integration Services Service caches (as DTS used to do) the list of available components and, among other things, OLE DB Providers, so that the designer doesn't need to waste time gathering those lists from the registry and the file system. I'm just speculating here, but I'm guessing that you might need to do something to refresh that cached list, such as stop and re-start the Service (or reboot). A little-known dialog box in DTS times has a "Refresh Cache" button, but I haven't seen an equivalent button in SSIS.

-Doug
|||Don't think that's the problem, I think the problem is that the driver doesn't get registered in the windows registery. Is there a way to add some keys to the windows registery to manually register the .dll files as OLEDB drivers?|||Hi, have you figured out that one?|||You say OLEDB drivers for .NET but that doesn't make sense to me. OLEDB is not a .NET provider instead it would be ADO.NET. Are the drivers OLEDB or are they .NET. If they are .NET then they wouldn't be available from our OLEDB source but from our DataReaderSource, which uses .NET connections. If they are indeed OLEDB then if they are not showing up then they are not registering themselves correctly. You could try regsvr32 to reregister them but you might have to contact MySQL support and ask them for help.

Thanks,
Matt

MySQL OLEDB Driver

Hi!

I have a small question, there are OLEDB drivers availible for .NET on the MYSQL download site. But when I install them I'm not able to see them inside SQLIS, I've tried them inside Visual Studio and they work fine.

Is there a way to get them "registred" or add them as a reference inside SQLIS so that I may choose them when setting up an OLEDB Source / Destination?For performance reasons, the Integration Services Service caches (as DTS used to do) the list of available components and, among other things, OLE DB Providers, so that the designer doesn't need to waste time gathering those lists from the registry and the file system. I'm just speculating here, but I'm guessing that you might need to do something to refresh that cached list, such as stop and re-start the Service (or reboot). A little-known dialog box in DTS times has a "Refresh Cache" button, but I haven't seen an equivalent button in SSIS.

-Doug
|||Don't think that's the problem, I think the problem is that the driver doesn't get registered in the windows registery. Is there a way to add some keys to the windows registery to manually register the .dll files as OLEDB drivers?|||Hi, have you figured out that one?|||You say OLEDB drivers for .NET but that doesn't make sense to me. OLEDB is not a .NET provider instead it would be ADO.NET. Are the drivers OLEDB or are they .NET. If they are .NET then they wouldn't be available from our OLEDB source but from our DataReaderSource, which uses .NET connections. If they are indeed OLEDB then if they are not showing up then they are not registering themselves correctly. You could try regsvr32 to reregister them but you might have to contact MySQL support and ask them for help.

Thanks,
Matt

MYODBC Drivers for sql server 2005

SO I installed the MyOdbc drivers 5.0 and 3.51 and then went into my Data Sources(ODBC) drivers in my Administrative Control Panel

I then proceeded to add the DSN Under System DSN and I also tried User DSN

When I try to use the import/export tool in my SQL Server 2005 Management studio I don't get presented with the MySql drivers at all for a source

why is this?

Trust me you don't need MySQL ODBC driver in SQL Server to move MySQL data to SQL Server, so tell me what you are trying to do and I will tell you how to do it. Hope this helps.|||

oh well that's pretty much it

The sites php with a mysql DB

I'm making it .NET with an MS SQL DB

So just copying the table data and structure to MS SQL

|||

You can use OLE-DB driver to import MySQL data and table structure if it is not in the import/export wizard look for it in DTS/SSIS, another option is to use OPENROWSET or OPENQUERY in SQL Server to connect to MySQL run a search for all of the above in SQL Server BOL(books online). Hope this helps.