Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

Python

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 Devart MySQL Lookup for an Oracle DB Source

SSIS Using Devart Data Flow Components for Oracle to MySQL Lookup Task

This article describes how to use the Data Flow components available from Devart.com to connect to an Oracle 11g database and use a MySQL 5.7 connection to lookup values from a table and then export the results for the Match and the No Match data to 2 Excel spreadsheet files. The lookup will be using the Country_Name field from an Oracle table as the source and then comparing the Country to a list of Countries from a table in a MySQL database.

Prior to beginning, you will need to download and install the Devart SSIS Data Flow Components. There is a free 30 day trial on the download of the product.

SSIS Project MySQL Lookup Task

To begin, opening Visual Studio, select a new BIDS project. Add Data Flow Task to the Control tab.

SSIS Oracle Connection Setup

Add a New Oracle connection by Right clicking the Connection Manager and selecting DevartOracle Connection.

Devart Oracle Connection

Click on the Direct checkbox to get the additional fields for the Port and SID boxes to show up. The user in this exercise is HR.

Devart Oracle Connection Setup

Go to the Data Flow tab and add an Devart Oracle Source and Right Click on it and select Edit.

Devart Oracle Source

Select the Devart Oracle Connection Manager from the Oracle Connection Drop down box at the top. Write a query or select tables from the left hand side.

Devart Oracle Source Query

Add a DevartMySQL Connection by Right clicking the Connection Manager and selecting New Connection.

Devart Oracle Source Query

Type in the Server, User and Password, and select the database to use for the Connection. Test the connection and if all is well, click OK.

Devart Oracle Source Query

Add a Devart MySQL Lookup to the Data Flow Task. Connect the Oracle Source arrow to the MySQL Lookup. Right click on it to Edit and select the MySQL Connection Manager for the Connection. Select Country as the table for the Lookup Object. Check next to the country Lookup Column and select the Referential Constraint as the Country_Name from the Oracle source and the Reference Column as the Country field from the MySQL table.

Devart MySQL Lookup Editor mappings

Add 2 additional connections in the connection Manager for 2 Excel files with column Headers. Add 2 Excel Destinations to the Data Flow and map the Lookup Match output and Lookup No Match Output arrows to each.

Setup Excel Destinations

Right click each Excel Destination to set the Connection Manager and Map the columns from the lookup to the Destination Excel files.

Excel Destination Editor

To map the output columns, simply click on the Mappings link on the left to view in the Destination Editor.

Excel Destination Editor

Run the package and then view each Excel File to see the results.

Excel Mapppings from Lookup

Contents of the Match File

Excel Match Results

Contents of the No Match File

Excel No Match Results

Devart SSIS Data Components allow processing of a variety of Data Sources for ETL operations which are not present in their Native applications or currently available in the SSIS as shipped.