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.
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.
Go to the Data Flow tab and add an Devart Oracle Source and Right Click on it and select Edit.
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.
Add a DevartMySQL Connection by Right clicking the Connection Manager and selecting New Connection.
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.
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.
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.
Right click each Excel Destination to set the Connection Manager and Map the columns from the lookup to the Destination Excel files.
To map the output columns, simply click on the Mappings link on the left to view in the Destination Editor.
Run the package and then view each Excel File to see the results.
Contents of the Match File
Contents of the No Match File
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.