SSIS2008: Using SSIS for managing MySQL data
-
In my current role, I'm working with mainly MySQL and PHP. Management would like to migrate over to the Microsoft stack and so I need to start showing how it can benefit us.
Part of this is changing difficult to debug iterative RBAR PHP scripts to sensible SSIS packages.
I read a blog that said it's best to use the Connector/Net extension from MySQL for using in SSIS. I've downloaded this and have started building the package. I've encountered a number of difficulties that I've been trying to overcome with tips from the blog of one of the SSIS development team.
So far I've set the RetainSameConnection property to True and put a SQL task in before the rest of the package to consider the " mark as a quote. This got rid of a problem with how SSIS was trying to insert data in a MySQL destination table. However, I suspect that this is now causing the next error I'm getting on the same insert task:
An exception has occurred during data insertion, the message returned from the provider is: There is already an open DataReader associated with this Connection which must be closed first.
I've googled this error and it commonly shows up with SQL Server dbs because of issues with a MultipleActiveResultSets property, but I don't think this applies in this instance. Other cases were where people were coding and connections weren't dropping correctly.
I'm having to persist the connection for the workaround recommended here (http://blogs.msdn.com/b/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx) to work but I think it is now causing this next error.
Does anyone have any idea how to bodge this bodge or if a different bodge is needed?
Thanks in advance
Steff
-
*bump*
-
Hi Steff,
In my opinion the best working MySQL provider is ODBC. You can read from ODBC thru the standard ADO.NET DataReader Source. For writing to MySQL, check the commercial CozyRoc ODBC Destination component. It supports bulk-load API and it is 30x faster compared to regular ODBC insert.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Thank you very much for the response. I've downloaded the extension pack and its working well. The table difference task and a few others are already proving invaluable!
The only thing I've noticed though is that now when SSIS determines column length for input and output columns on tasks, it is always picking a string length half that of the defined column. It's very odd, I'm still waiting for the CozyRoc forum to be whitelisted and my googling isn't picking anything up on this. It's getting very tedious to have to amend these strings all the time. Have you heard of this issue before?
-
stephanie.sullivan (8/18/2010)
Thank you very much for the response. I've downloaded the extension pack and its working well. The table difference task and a few others are already proving invaluable!The only thing I've noticed though is that now when SSIS determines column length for input and output columns on tasks, it is always picking a string length half that of the defined column. It's very odd, I'm still waiting for the CozyRoc forum to be whitelisted and my googling isn't picking anything up on this. It's getting very tedious to have to amend these strings all the time. Have you heard of this issue before?
Hi Stephanie,
Thank you for your kind words!
I have checked the CozyRoc forum and there is no message posted by you. If you want your messages not to be moderated you can become member of the group and your messages will post unrestricted. This is for protection against spam.
When you talk about the half length string, which component you are referring to? You can contact CozyRoc's support from here: http://www.cozyroc.com/contact
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply