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 comments on “HOWTO: Connect to MySQL in SSIS

  1. Ismael Toledo on said:

    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. shiva ramani on said:

    good instruction. How to push data to MySQL from SQL 2005

  3. 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

  4. Vincent Chang on said:

    Well done! Very Clear

  5. 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,

  6. BIDS = SQL Server Business Intelligence Development Studio

  7. What is BIDS??? (step #2)

  8. @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.

  9. so then ?? how to use that dataGrid ??

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>