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

   

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

   

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”

   

6. Rename the connection to “MySQLDB”

   

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

   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

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

    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

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

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

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

    Add Recordset Destination

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

    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

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

19. Right-click the green Data Flow Path and choose “Data Viewers…”
    

    Data Flow Path Data Viewers

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

    Data Flow Path Editor

21. Under the General tab, select Grid and press “OK”
    

    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

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.