Transfer data from MySQL to SQL Server Using SSIS

I will be doing a series of tutorials (some simple, some more involved) in SSIS in the next little while. In addition to step by step instructions, I will also be providing the video tutorial equivalents which will be posted at the Black Ninja Software website. Once the video tutorials are up, I will be updating the individual links.

Now for this simple problem. You have a MySQL database, and you want to be able to dump data from it to SQL Server using SSIS.

Here are the steps:

1. If you haven’t already, download and install MySQL Connector for ODBC.

2. Create a DSN for your MySQL

a. Go to Start > Administrative Tools > Data Sources (ODBC)
b. Create a new User DSN (in my case I called it MySQL55)


3. Create a new package in SSIS, drop a data flow task.

4. Double click on your data flow task. In the data flow tab, drop an ADO.NET data source.

5. Double click your ADO.NET data source to configure. Under the connection manager, navigate to your MySQL DSN.

6. Under data source, choose SQL Command, and type a query that gets all the records you want from your MySQL table(s). Click on Preview after you type your query, just to make sure you’re getting the correct data.

Don’t choose table or view. (I’m getting errors on this, and haven’t had a chance to troubleshoot yet..) When I use table or view, and click on Preview, I get this error.

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”city”‘ at line 1 (myodbc5.dll)

YMMV. If you get this error, stick to SQL Command for now. When I figure out why this is so and how to fix it, I will post an update.

7. Click on Columns to map your columns from your query, then close your ADO.NET Source Editor.

8. Drag over an OLEDB destination. Configure this to point to your SQL Server destination, then map the columns.

9. Run the package.

Hope this helps!

Advertisements

3 comments

  • So the heavy lifting here is provided by the MySQL Connector for ODBC. I used this a few months ago to export a mysql table to MSAccess (not for me or my shop! as a favor to a friend) — ODBC is the great database interchange enabler.

    Like

  • Had the same issue about this error 42000, and finally figured out that you should set the MySQL global mode to ansi_quotes:

    set global sql_mode=ansi_quotes;

    Like

  • ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.10]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”city”‘ at line 1 (myodbc5.dll)

    I also encountered the same problem,but later I resolved it by changing the source from ADO.NET source to ODBC Source ,The error was resolved and my problem was solved

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s