How to Export Data from SQL Server to MySQL through SSIS?

SQL Server Integration Services (SSIS) is a widely-known tool for data migration, transformation, and integration. A widespread example of an SSIS application is that of exporting data from an SQL Server database to a MySQL database.

Although this process is often complicated, it is possible to simplify it with the right measures. This article is a step-by-step approach to getting data exported from SQL Server to MySQL using SSIS.

Before you begin, ensure you have the following:

• SQL Server: Installed and running with the database you want to export data from.
• MySQL Server: Installed and running with the database where you want to import the data.
• SSIS: Installed as part of SQL Server Data Tools (SSDT).
• MySQL Connector/ODBC: To migrate the data from the SQL server to MySQL we are using ODBC drivers. we need to download the ODBC driver for MySQL. You can download it from Google.

Let’s suppose I have an email table with 10 records in my SQL Server and I want to migrate it to SQL Server SSIS database to my SQL test database.

Below are the steps given to export Data from SQL Server to MySQL through SSIS:

• Create a Data Flow Task
• Configure the OLE DB Source
• Configure the ADO.NET Destination
• Run the Project
• Verify Data in MySQL

Step 1: Create a Data Flow Task

1. Drag and drop a Data Flow Task from the SSIS Toolbox to the Control Flow tab.

2. Double-click the Data Flow Task to open the Data Flow tab.

Step 2: Configure the OLE DB Source (To read the data from the SQL server we use OLE DB Source)

1. In the Data Flow tab, search for the OLE DB and then drag and drop an OLE DB Source from the SSIS Toolbox.

2. Double-click the OLE DB Source to open the OLE DB Source Editor.

3. Select the SQL Server connection manager.

4. Click on new.

5. Choose the Table or view option and select the emails table from the dropdown list.

6. Click Columns on the left pane to ensure all columns are selected.

7. Click OK to close the dialog.

Step 3: Configure the ADO.NET Destination (To insert the data into the MySQL we use ADO.NET destination)

1. In the Data Flow tab, drag and drop an ADO NET destination and connect the OLE DB source with ADO.NET destination.

2. Right click on ADO.NET destination and click edit.

3. Select the MySQL ODBC connection manager configured.

4. Click the the New button in connection manager.

5. Again click new to create a new ODBC connection.

6. From the provider type select ODBC data provider and click ok.

7. Select use connection string and paste the below connection string into that. Remember to add your password in place of your_password.

DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;DATABASE=test;UID=root;PASSWORD=your_password;

8. Add username and password in login information and click on test connection.

9 Click ok and again click ok.

10. The ADO.NET destination editor screen opens. In use a table or view option you can see in the dropdown that no table is there so to create a new table you click on new and then add the SQL code to create new table and then click ok.

CREATE TABLE emails (    
id INT NOT NULL PRIMARY KEY,   
First_name VARCHAR(50),   
Last_name VARCHAR(50),   
Email VARCHAR(50),
Gender VARCHAR(50),
);

11. Click on mappings on the left side and click ok.

Step – 4 Run the Project

1. Save the SSIS package. and click on the start button to run the SSIS package.

2. Ensure that the data is transferred successfully without errors.

Step -5 Verify Data in MySQL

1. Open your MySQL client (such as MySQL Workbench).

2. Connect to your MySQL server and select the database where the emails table is located.

• Error Handling: Implement error handling and logging within SSIS to capture and address any issues during the data transfer.
• Performance Tuning: Optimize your SSIS package for performance by adjusting batch sizes, using parallel execution, and indexing tables appropriately.
• Scheduling: Use SQL Server Agent to schedule the SSIS package execution for regular data transfers if needed.

Exporting data from SQL Server to MySQL using SSIS involves several steps, including configuring connections, creating data flow tasks, and handling data type mappings. By following this guide, you can efficiently transfer data between these two database systems, leveraging the power of SSIS for seamless data integration.

Implementing these steps ensures a smooth and effective data export process, enabling you to maintain data consistency and integrity across different database platforms.