mySQL to MS SQL 2005 - ODBC?

Hi,

Posted this at the tail end of another thread but that one appears to have died. Thought I'd try again since my question was a little distinct from that one anyway, but apologies if I've committed forum sacrilege.

I am having a heck of a time trying to migrate a fairly simple MySQL DB to SQL Server I am using SQL Server 2005 Standard Edition SP1.

I tried following MS' instructions in this article:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx

hoping that what worked for 2000 would work for 2005, since the principles seem sound. No luck. I can create the ODBC link, but SQL Management Studio, the '.NET provider for ODBC' option doesn't give me the choice to copy tables - only to write a query.

I tried making sense of the on-line help and was able to create the ODBC connection in the 'Business Intelligence Development Studio' but I have no idea what I am looking at with respect to creating control & data flow in an SSIS package. All I know is that I could do what I am trying to do in SQL Server 2000 quite easily - the MySQL database in question is quite simple (but not small). I am pretty amateurish when it comes to SQL administration, but this seems to me unnecessarily difficult.

I tried the MySQL OLE connector from sourceforge and was able to properly set up a test connection and get the 'copy tables' option, but then when I hit 'next' I get:

MYSqlProv 3.9 failed with no error message available, result code E_ABORT(0x80004004) (System.Data)

Am I missing some stupendously easy way around this, or did I torpedo this client when I promoted moving them from MySQL to MS SQL Server on account of my not being a full-time SQL Server DBA who knows how to use the Business Intelligence Development Studio?

did you try using the import/export wizard in management studio?|||

Yes. The instructions in the support kb link above basically say to use the import/export function (though it's referring to 2000, so it's not the studio) and that the ODBC link should be there. In Management Studio, the only reference to the ODBC link is the '.NET Provider for ODBC' which 'works' in that I can query the data source, but I can't copy tables directly. There are dozens of tables.

What I am really looking for is a simple, straightforward ODBC data link provider that works with Management Studio. SQL Server 2000 had it.

|||

After trying to find a solution to this that didn't involve writing over three dozen queries, I installed MS Access, used a regular ODBC link to import the MySQL database to Access (this took about ten seconds), and then the SQL Server 2005 import/export wizard to import from Access.

SQL Server lost this round. I'll be the first to admit I'm no pro with the myriad of things SQL Server does tremendously well, but this one is so basic it's astonishing that two earlier products can perform this basic task with much more ease and simplicity than SQL Server can - assuming SQL Server can do it at all. I certainly couldn't get it to.

|||

I wholeheartedly agree. Importing data from a mySQL database should be a matter of a few seconds in setup, and I'm sitting here requisitioning a copy of MS Access so I can get data out of a database I used to be able to import easily.

They seriously dropped the ball on not having *real* ODBC support for import services like they've had in previous versions.

Wake up! If I'm migrating TO SQL Server you should be HAPPY and do everything in your power to ensure that the import experience is as seamless as possible. Because now, if I don't get approved for Access, well we're going to use MySQL instead of SQL Server 2005, or any later version thereof.

I hope someone there recognizes the size of this... blunder.