What is Easiest way to import 150+ MySQL tables into SQL Server via SSIS

Question

What is Easiest way to import 150+ MySQL tables into SQL Server via SSIS

sotn

SSCrazy

Points: 2672
More actions
December 2, 2008 at 5:05 am

#125253

I have to take a mySQL database (150+ tables) and place into a SQL Server database
I know I can create 1 ado net source (data flow source) per table and point each one to a SQL server destination and that will create the SQL tables and then populate them with data. This is because these data sources are pointing to a specific table or view
But this will be very time consuming and just feels wrong.
There must be an easier way to import all data in the MySQL database into the SQL Server database, creating the tables if needed.
Any pointers as to the best way to acheive this data move?
TIA

Viewing 4 posts - 1 through 3 (of 3 total)

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

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

sotn (12/2/2008)
I have to take a mySQL database (150+ tables) and place into a SQL Server database
I know I can create 1 ado net source (data flow source) per table and point each one to a SQL server destination and that will create the SQL tables and then populate them with data. This is because these data sources are pointing to a specific table or view
But this will be very time consuming and just feels wrong.
There must be an easier way to import all data in the MySQL database into the SQL Server database, creating the tables if needed.
Any pointers as to the best way to acheive this data move?
TIA

Use Import / Export Wizard, to transfer your MySQL data into SQL Server, it is available in SQL Server 2000 / 2005 / 2008 versions.

sotn SSCrazy Points: 2672 More actions · Answer 2

Hi,

I had already tried that but although I can see the tables when I try to 'preview' the table I get an error message saying 'you have an error in your sql syntax

I have 'ticked' a table listed and clicked on 'preview' so I am not manually doing any sql, the internal system is so I have no control over how it is querying the mysql database.

I get the same problem in SSIS, but if I change it drom table/view to sql command and manually type select * from mysqltablename then it works ok.

sotn SSCrazy Points: 2672 More actions · Answer 3

I have actually found in SQL BOL that using the .NET ODBC as the data source you cannot select 'copy tables' as it cannot see column information and you must specify a single query.