Import data from MySQL into SQL Server

Question

Import data from MySQL into SQL Server

kim.talley

SSC-Addicted

Points: 407
More actions
February 3, 2009 at 3:36 pm

#130273

I have an SSIS package that needs to run periodically to pull all rows from a table in MySQL. This table is updated frequently on MySQL and I am having locking issues. I have created the connection and am using the datareader source and the package works fine most of the time.
I would like to change the transaction level on this task to 'read uncommitted' (similar to nolock in SQL). My problem is that I can't find a way to set this using this connection. I tried executing a SQL task to set the transaction level just before my data flow but MySQL sees this as separate connections and shows the transaction level as 'repeatable read' for my select. I have tried changing the IsolationLevel on my dataflow task but this doesn't seem to have any effect.
Does anyone have any experience with this or have a better suggestion for reading table data from MySQL?
Thanks in advance - you guys are the greatest and always have great advice!
Kim

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

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

kim.talley (2/3/2009)
I have an SSIS package that needs to run periodically to pull all rows from a table in MySQL. This table is updated frequently on MySQL and I am having locking issues. I have created the connection and am using the datareader source and the package works fine most of the time.
I would like to change the transaction level on this task to 'read uncommitted' (similar to nolock in SQL). My problem is that I can't find a way to set this using this connection. I tried executing a SQL task to set the transaction level just before my data flow but MySQL sees this as separate connections and shows the transaction level as 'repeatable read' for my select. I have tried changing the IsolationLevel on my dataflow task but this doesn't seem to have any effect.
Does anyone have any experience with this or have a better suggestion for reading table data from MySQL?
Thanks in advance - you guys are the greatest and always have great advice!
Kim

Kim,

You didn't mention what type of connections you use. Can you please right-click on your connection manager and look for RetainSameConnection property. By default it is set to false. If you find it, change it to true and try again. If you don't find it, try using a different connection type.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

Sun bought MySQL last year and the new ADO.NET provider comes with native MySQL transactions which should be atomic unit of work like T-SQL which is different from the version in SSIS and System.NET. I am assuming you know the SSIS version require MSDTC by default while the MySQL version will not require MSDTC. I don't have MySQL here but you should download the new VS2008 connector and try using ADO.NET.

http://dev.mysql.com/doc/refman/6.0/en/connector-net-examples.html

Kind regards,
Gift Peddie

kim.talley SSC-Addicted Points: 407 More actions · Answer 3

I am using the ODBC data provider. I have tried changing the RetainSameConnection property but this causes a validation error on my data reader.

For the new ADO.NET connector, I cannot find a 64 bit version. Do you know if this exists?

Thanks,

Kim

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

kim.talley (2/3/2009)
I am using the ODBC data provider. I have tried changing the RetainSameConnection property but this causes a validation error on my data reader.
For the new ADO.NET connector, I cannot find a 64 bit version. Do you know if this exists?
Thanks,
Kim

What is the error that you get?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

Sun's Solari comes in 64bits I have run Oracles run in Sun's Solari but I am not sure if there is 64bits version of that driver because it is free. But I can tell you such transactions using an ODBC connection may be a problem, it maybe the reason for all your problems because ODBC is very old.

You really don't need x64 version because I think your MySQL is also x86 so you actually need the x86 version because when everything you are running is not x64 you need to build x86. That could also stop your package execution.

Kind regards,
Gift Peddie