SSIS Visual Studio 2015 Community Edition- MySQL Connection
SSIS MySQL Connection Built Into Visual Studio 2015 Free Community Edition and SQL Server Data Tools
This article describes how to use the Free Visual Studio 2015 Community Edition to connect to a MySQL database and transfer data from a MySQL table into an Excel Workbook. The ADO .NET connection is also available in SQL Server Data Tools if you are using SQL Server 2014.
SSIS MySQL Connection with Visual Studio 2015
Right click in the Connection Managers section in SSIS and select New ADO .Net Connection.
Select MySQL Data Provider from the list of options and click OK.
In the Connection Manager, add the Server Name, Database Name, UserID and Password and select True in the PersistSecurityInfo box. If PersistSecurityInfo is not set to True, an error of "Test connection failed because of an error initializing provider..." will show up when pressing the Test Connection button and will cause the execution of the SSIS package to fail.
Set up SSIS Excel Connection and File for MySQL Data
Right click on the Connection Manager and select an Excel Connection from the list and click Add.
Create an Excel file point the Excel file Path to it. You can create the column headers later. Click OK to close.
Right click the ADO Net Source and click Edit to set up the Query in MySQL.
Select the MySQL Connection created earlier and change the Data access mode from Tables or Views to SQL command and type in the query to use (or Browse to use a .sql file with the code). This query uses the sample table actor from the sample schema "sakila" in my MySQL 5.7 Database.
Click Preview to view the names of the fields. Add these field names in the Excel file created with the connection manager.
Add an Excel Destination to the SSIS Data Flow. Drag the arrow from the ADO NET Source to the Excel Destination and right click on it and click Edit.
Map the Input and Destination Columns if they do not automatically map. Click OK to Close.
Run the SSIS package to transfer the data from MySQL into the Excel File.
Open the Excel File to view the results of the data from a MySQL Database transferred into Excel file!
The Connections for MySQL and Oracle databases in Visual Studio 2015 are welcome additions to Developers using SSIS or SSRS. These easy to use Connections allow developers to import and export data from "outside" database sources without using a 3rd party tool.