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.
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.
9. Run the package.
Hope this helps!