HOWTO: Connect to MySQL in SSIS

While Microsoft provided connectors for Oracle, Teradata, and SAP BI for SSIS 2008, there are many other database systems left out of the mix. Fortunately, SSIS is exceptionally flexible in connecting to various data sources and allows other vendors to provide native support. The MySQL team did just that with Connector/NET 6.0, their ADO.NET provider. This tool allows us to use the the ADO.NET connections in SQL Server Integration Services to easily connect to MySQL. This is a walk through on how to connect to MySQL with SSIS 2005 utilizing the Connector/NET 6.0 ADO.NET provider.

  1. Download and install MySQL Connector/NET 6.0
  2. Start a new Integration Services project in BIDS
  3. Right-click in Connection Managers and create a new ADO.NET Connection

    New ADO.NET Connection

  4. In the Provider dropdown, expand .Net Providers and select MySQL Data Provider. Press "OK"

    MySQL Data Provider

  5. Fill out the Server name, User name, Password and select the database name for the target MySQL server. Be sure to test the connection and press “OK”

    Connection Manager Connection Info

  6. Rename the connection to “MySQLDB”

    Connection Managers MySQLDB

  7. Open up the Toolbox and drag a Data Flow Task from the toolbox onto the Control Flow surface

    Add Dataflow Task
    Add Dataflow Task
  8. Double-click the Data Flow Task to switch to the Data Flow view
  9. Create a new variable, “MySQLResult” with the Data Type of Object. We will be using this as the final destination for the data, so we don’t need to connect to a file or database to store the data from this test

    MySQLResult Variable
    MySQLResult Variable
  10. Drag a new DataReader Source component onto the Data Flow surface

    Add DataReader Source
    Add DataReader Source
  11. Double-click the DataReader Source to open the Advanced Editor. On the Connection Managers tab, select the previously-created MySQLDB connection

    DataReader Source Connection Managers
    DataReader Source Connection Managers
  12. Switch to the Component Properties tab and enter the SQL query in the SqlCommand property. Note that the query must be compatible with MySQL syntax, not SQL Server.
    DataReader Source Component Properties
    DataReader Source Component Properties
  13. Switch to the Column Mappings tab to verify that the query is successful and the all the columns were pulled from the database. When done, press “OK”.

    DataReader Source Column Mappings
    DataReader Source Column Mappings
  14. Create a new Recordset Destination by dragging it from the toolbox to the Data Flow surface

    Add Recordset Destination
    Add Recordset Destination
  15. Drag the green Data Flow Path from DataReader Source to Recordset Destination, so they connect

    Connect Source to Destination
    Connect Source to Destination
  16. Double-click the Recordset Destination to open its Advanced Editor
  17. Under Custom Properties, select the dropdown for VariableName and select the variable we created before, User::MySQLResult

    Recordset Destination Component Properties
    Recordset Destination Component Properties
  18. Switch to the Input Columns tab and select those columns that you want stored in the Recordset Destination. When complete, click “OK”
    Recordset Destination Input Columns
    Recordset Destination Input Columns
  19. Right-click the green Data Flow Path and choose “Data Viewers…”

    Data Flow Path Data Viewers
    Data Flow Path Data Viewers
  20. Select “Data Viewers” from the left pane and click the “Add…” button

    Data Flow Path Editor
    Data Flow Path Editor
  21. Under the General tab, select Grid and press “OK”

    Configure Data Viewer
    Configure Data Viewer
  22. Run the package
  23. If you’ve done everything correctly, you should see a Data Reader Output Data Viewer window pop up with the contents of the query we specified earlier.

    Data Viewer Output
    Data Viewer Output

SQL Server Integration Services makes connecting to other systems very easy. The MySQL ADO.NET provider works well, but requires more configuration than a native Source component.

9 thoughts on “HOWTO: Connect to MySQL in SSIS

  1. My friend thank you for your time, this note make me to reach my goal, i was traying to find the correct driver for Integration Services over Mysql.

    Many notes talk about the Connector/ODBC but the correct connector is Net.

    Great Page.

    Regards
    Ismael Toledo
    From Ismael

  2. Hi,

    This is a great article to introduce the mySQL in SSIS. I found it to be very helpful.

    However, I am having trouble trying to figure out how to use this mySQL ADO in the Data Transformation section inside the Data Flow?

    Basically, based on the result of a ConditionalSplit, it will either retrieve data from mySQL or doing something else.

    I was able to retrieve data using this connector as the Source or Destination, but not in the Transaformation.

    Any suggestion?

    TIA,
    Ting

  3. I don’t seem to have the DataReader Source in my toolbox, only the destination. Even if reset the toolbox it doesn’t seem to appear.

    Has this option been removed from 2008??

    Thanks,

  4. @cesar

    The Data Viewer is just a way to visualize the data – you’re not supposed to extract data from it.

    What you probably want is an OLE DB Destination or Flat File Destination instead of the Recordset Destination to permanently store the data somewhere.

Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>