How to Export to MySQL in SSIS

An ADO connection uses a .NET provider to expose data sources in a SSIS package.

SQL Server Integration Services is SQL Server 2008 R2’s Extract, Transform and Load tool for integrating information across data sources. You can export files from SQL Server to MySQL using Business Intelligence Development Studio to create SSIS packages. SSIS packages consist of two logics flows: control and data. The control flow directs sequential processes associated with the data flow and other objects in a package. The data flow allows for record level data management in the package. Before assembling the package, though, you must first create ADO connections for the data source, MS SQL Server 2008 and the destination, MySQL.

Instructions

1. Click on the Windows Start button, point to Microsoft SQL Server 2008 and click on “SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO.”

2. Point to “New” and select “Project” from the “File” menu. When the Project Dialog box appears, select “Integration Services Project” from the list of Visual Studio Installed Templates. Enter a descriptive name in the “Name” field and click the “OK button.”

3. Right-click the area under the Connection Managers tab, which is located at the bottom center of the Package Designer Screen.

4. Select “New ADO.Net Connection” from the shortcut list that appears.

5. Click the “New…” button on the Configuration Manager Editor window to create a new connection manager.

6. Configure the settings for MS SQL Server 2008 (the data source) by accepting the default setting in the “Provider” field, entering an appropriate server name in the “Server Name” field and entering an appropriate database in the “Select or enter a database name.” Click “OK” to confirm ADO.Net Connection settings for the data source. Click “OK” again to return to the Package Designer Screen.

7. Right-click the area under the Connection Managers tab again. This time, you will be creating an ADO.Net Connection for MySQL (destination).

8. Select “New ADO.Net Connection” from the shortcut list that appears.

9. Click the “New … ” button on the Configuration Manager Editor window to create a new connection manager.

10. Configure the settings for MySQL (the data source) by selecting “.Net Providers\odbc data Provider” in the “Provider” field and choosing the appropriate data source under the Data Source Specification heading. Under the Login Information heading, enter the username and password for the data source. Click to confirm settings. Both connections will now appear in the Configuration Manager Editor window. Click “OK” again to return to the Package Designer Screen.

11. Drag the “Data Flow Task” from the Control Flow Items under the Toolbox to the design area of the screen. Next, click on the Data Flow tab and select “ADO NET Source” and “ADO NET Destination” under Data Flow Destinations in the Toolbox area.

12. Right-click the “ADO NET Source” and select “Edit” from the shortcut menu that appears. Select the table in the data source that contains the data you would like to export in the “Name of the table or the view:” dropdown field. Click “OK” to confirm settings and return to the Package Designer Screen.

13. Right-click the “ADO NET Destination” and select “Edit” from the shortcut menu that appears. In the Connection Manager field, select the destination database from the list if it is not selected by default already.

14. Click the “New … ” button next to the “Use a table or view:” dropdown field to create a new destination table to export the data from the data source. Once you click the “OK” button, you will receive a warning message indicating that the information is not sufficient and may need to update column settings in the “Create Table” window that appears. Click the “OK” button.

15. Remove the quotes in the “Create Table” window, add columns that map to the data in the data source and Click the “OK” button to continue.

16. Select “Mappings” to activate the Mappings page. Verify that all columns are mapped between data source and destination, and click “OK” to continue.

17. Save the SSIS package using the “Save Copy of Package.dtsx as” option on the “File” menu.

18. Change global settings for SQL Mode to “ANSI” in MySQL. Enter “SET GLOBAL sql_mode= ‘ANSI’” in the command line in MySQL.

19. Right-click the Package file under SSIS Packages in the Solution Explorer window. Select “Execute Package” from the list to complete the transfer. Once execution is complete, if the Data Flow Task control flow item is green, this means that the transfer was successful. If the Data Flow Task is red that means that an error occurred and the transfer was unsuccessful. For unsuccessful transfers, use the Output window that appears at the bottom of the screen to troubleshoot errors.

References

• Anirudh Tamsekar: SQL Server Integration Services 2008(SSIS) and MySQL
• Microsoft: SSIS Tutorial: Creating a Simple ETL Package
• Microsoft: SSIS Process Control
• Microsoft: ADO.NET Connection Manager
• Microsoft: Understanding the Components of an Integration Services Package
• Microsoft: How to: Run the SQL Server Import and Export Wizard