At times, you may want to import data from MySQL into SQL Server. In this case, MySQL OLE DB Provider can be used so that it will save you a lot of valuable time. The following sections will show you how to use MySQL OLE DB Provider with DTS to import MySQL data into SQL Server databases and discuss some of the limitations as well.
Using MySQL OLE DB Provider with DTS
Using MySQL OLE DB Provider with DTS is extremely easy. Here all simply steps you need to take in order to import MySQL data into SQL Server databases.
Start DTS Wizard and select MySQL Provider as the Data Source.
The click Properties button, fill in Data Source, User name, password and Initial Catalog (database name), then click OK.
Click Next, select Microsoft OLE DB Provider for SQL Server and the target database name, then click Next.
On this wizard below, there are two options: 1. Copy data from one or more tables or views or 2. Write a query to specify the data to transfer. Both options have their advantages and trade off. Option 2 data type mapping is better than option 1, but you may run one recordset at a time. With option 1, you may transfer data from multiple tables and/or views, but quite often, you need manually map data types between MySQL and SQL Server. Let us select option 2 - Write a query ... and then click Next
Write your query and click Next
You are getting the Source Tables and views.
Click Edit button to view data type mappings. Correct them if they are not mapped properly. Then click OK. Click Next
Now, you have an option to save the DTS package. By default, the package will not be saved. CLick Next
Before you hit Finish, the DTS summary is shown below.
The final step is the exectution
Data Tpe Mappings Between MySQL and SQL Server
MySQL Data Type
SQL Server Data Type
BIGINT
BIGINT
INTEGER
INTEGER
MEDIUMINT
INTEGER
SMALLINT
SMALLINT
TINYINT
TINYINT
VARCHAR
NVARCHAR
CHAR
NCHAR
TEXT
NTEXT
BLOB
IMAGE
FLOAT
FLOAT
REAL
REAL
DATE
DATETIME
TIME
NCHAR
DATETIME
DATETIME
TIMESTAMP
DATETIME
Limitations
The data type mapping above gives us a guideline on how do we map data columns between MySQL and SQL Server. The current limitations are as follows:
Column size cannot be more than 3999
Columns with TEXT and BLOB data types cannot be transfered. However, you may use MySQL as Linked Server to workaround this limitation.
