Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

SSIS ADO Connection

Select MySQL Data Provider from the list of options and click OK.

SSIS MySQL Data Provider

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.

SSIS Connection Manager

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.

SSIS Connection Manager Excel

Create an Excel file point the Excel file Path to it. You can create the column headers later. Click OK to close.

SSIS Excel  Connection Manager

Right click the ADO Net Source and click Edit to set up the Query in MySQL.

SSIS ADO Net Source

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.

SSIS MySQL Data Connection

Click Preview to view the names of the fields. Add these field names in the Excel file created with the connection manager.

SSIS Preview Excel Data

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.

SSIS Data Flow

Map the Input and Destination Columns if they do not automatically map. Click OK to Close.

SSIS Data Mapping

Run the SSIS package to transfer the data from MySQL into the Excel File.

SSIS ADO Net MySQL to Excel

Open the Excel File to view the results of the data from a MySQL Database transferred into Excel file!

Excel File with MySQL Data Returned

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.