Share via

MySQL Tables Copy to MSSQL via SSIS

jtech 26 Reputation points
2023-01-04T15:53:01.793+00:00

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!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,600 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 26,036 Reputation points
    2023-01-04T17:21:02.3+00:00

    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.
    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. jtech 26 Reputation points
    2023-01-04T20:32:07.443+00:00

    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!

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.