MySQL Tables Copy to MSSQL via SSIS

Question

Hey everyone,

Full disclosure, I am new to Databases and SSIS, but what I am trying to figure out is how I can copy my Database tables from within MySQL to MSSQL using an SSIS package. I've only found a couple of forums online where this has been documented/attempted to some degree, but the instructions that were provided didn't match the options I have in Visual Studio to successfully complete things.

I've got a current working environment where I can take a flat file (.csv), pull it in to SSIS and have it populate/update a table in MSSQL. It's my understanding that one of the core purposes of SSIS is the ability to take Databases from various platforms (Oracle, MySQL, MSSQL, .csv, etc...) and connect them for the purpose of analyzing all the data in one place, which can then allow you to do things like creating detailed reports. In my case, I now need the connection from MySQL to do the same thing I am doing with the .csv - to populate/update a table within MSSQL.

• Here are a couple of links I found when browsing Google to try and find a way to do this, but the instructions are either not detailed enough, or specific enough to my versions to be able to get through them because only portions of the steps are even available to me within the Visual Studio - meaning, (to me) that either my version is newer and the names of things have changed, or the article is just not detailed enough to walk me through the process with my limited understanding.
  https://www.cdata.com/kb/tech/mysql-ssis-task-import-2008.rst
  https://smallbusiness.chron.com/import-csv-ssis-46849.html

I am using the following software versions:

• MSSQL 2019
• SSDT 2022
• MySQL 8
• mysql-connector-net-8.0.31

The furthest I have been able to get so far is having the "Connection Manager" point to the machine that has MySQL on it, and verified that the connection is successful. From there, all the documents I have read say to use the "Data Reader" or "Data Reader Source", or something to that degree. However, my only options that I see on either the Control Flow or Data Flow tabs are:

• OData Source
• ODBC Source
• ADO NET Source
• CDC Source
• OLE DB Source
• DataReader Destination

I've basically tried all of these (one-by-one), and tried to see if I could get things to work, but despite having the mysql drives installed (needed before you can even setup MySQL as a connection) and the appropriate Connection Manager setup, MySQL doesn't exist as a drop-down nor setting anywhere within the properties of any of the above sources.

Any guidance would be greatly appreciated!

Answer 1

Hi @JeremyWarfel-5231,

You need to tag your question as 'Integration Services'.

I have been able to get so far is having the "Connection Manager" point to the machine that has MySQL on it, and verified that the connection is successful.

I am assuming that you are using some kind of ODBC driver for that connection.

You need to do the following:

1. Add a Data Flow Task (DTF) to the SSIS package.
2. Go to that Data Flow Task and add ODBC Source using MySQL connection.
3. Add OLE DB Destination using SQL Server connection in the same DTF.
4. Connect ODBC Source and OLE DB Destination, and map corresponding columns.

Answer 2

I just now finally got this working after a lot of trial and error. The original Source I found for the Data Flow that talks to MySQL was correct, which was called "ADO NET Source". From there, having added a connection to the MySQL machine by IP address, and using the "SQL command" drop-down with a command saying: <SELECT*FROM yourspecifictable.table> to select the desired tables was needed. Then, the recommendation from YitzhakKhabinsky to use the Data Flow Destination of "OLE DB Destination" ended up being the correct "Destination" to use, which helped steer me in the right direction, but configuring it to actually dump into my MSSQL took following the last 4 steps of this link to get it to work: https://www.cdata.com/kb/tech/mysql-ssis-task-import-2008.rst

On the above link, they didn't mention that in Step #3, you need to click "New" to pull all the columns in before proceeding to Step #4. Maybe this is because that is common sense for most people, but to me (as a beginner) I was needing to be a little more spoon fed on this.

I then executed the Package and it pulled all 122 rows of data from my sample MySQL Database and threw it into a new table within MSSQL. I am pretty impressed to say the least, haha! Thanks for your help on pointing me in the right direction YitzhakKhabinsky!