Using MySQL Provider With SQL Server DTS Wizard

Introduction

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.

  1. Start DTS Wizard and select MySQL Provider as the Data Source.
  2. The click Properties button, fill in Data Source, User name, password and Initial Catalog (database name), then click OK.
  3. Click Next, select Microsoft OLE DB Provider for SQL Server and the target database name, then click Next.
  4. 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
  5. Write your query and click Next
  6. You are getting the Source Tables and views.
  7. Click Edit button to view data type mappings. Correct them if they are not mapped properly. Then click OK. Click Next
  8. Now, you have an option to save the DTS package. By default, the package will not be saved. CLick Next
  9. Before you hit Finish, the DTS summary is shown below.
  10. 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: