Integration

Import CSV file into Oracle database with Dell Boomi

Load CSV file into Oracle Database

In this blogpost I will describe how to load data from a CSV file into an Oracle Database with Dell Boomi. This integration runs completely in the local network. No data is processed within the Boomi Cloud.

I use an Oracle XE database with example schemas installed. The product_information table is located in the OE schema. The Atom inside the VM doesn’t have an Oracle JDBC driver installed. So it is necessary to install the required driver (18c) in our environment. The configuration steps include everything needed to achieve this.

Prerequisites

  1. Setup vagrant image with local Atom and Oracle XE database (see previous post for details)
  2. Login with SSH into the virtual machine
    vagrant ssh node1
  3. Start Atom locally
    cd /home/vagrant/Boomi_AtomSphere/Atom/Atom_javadev01_esentri_com/bin
    ./atom start

Development

Login to AtomSphere in a web browser.

Create a new folder CSV Database Import

Click Save

Download Oracle JDBC driver from https://www.oracle.com/database/technologies/appdev/jdbc-ucp-183-downloads.html

Navigate to Settings > Account Libraries and upload all required jar files from the previously downloaded Oracle JDBC driver.

Navigate to Integration and select New Component for your account. Select Custom Library for Type:

Click Create.

Add all uploaded jar files and click Save

Deploy the Custom Library to the environment

Verify that all jar files are loaded in ATOM_HOME/userlib. Afterwards restart the Atom.

Create a new component of Type Connection and select Database as connector.

Select Custom driver type, because we want to connect with service name to the Oracle database:
Class Name: oracle.jdbc.OracleDriver
User: OE
Password: <secure password>
Connection URL: jdbc:oracle:thin:@<host>:<port>/<service_name>All other options have default values.

Create a new component of Type Profile and select Database as Profile Format. Name the new component Products.


Click Create.

In Options view select Write as Execution Type. Go back to Data Elements and select Dynamic Insert in field Type. Afterwards click Import.

Select the appropriate Atom from the list and select the right database connection in the Connection field. Afterwards click Next. Then select the right database table from the list. In this example select OE.Product_Information.

Select all fields from the table PRODUCT_INFORMATION.

Review the profile configuration and finish the profile creation by clicking on Save and Close.

Create a new Map and choose Profile (Flat File) “Product CSV” as source and Profile “Products” (Database) as target. Map the fields like seen in the following figure:

In this particular example a lookup is needed for the category name. In order to achieve this add a new function to the map and select Lookup from the Category list. Within the Functions area select Sql Lookup.

Define Input and Output parameters for the lookup. Parameters in SQL statement are used with “?” and mapped according to the order defined in Inputs. The same applies to the Output definitions. The mapping of the SQL result is mapped in order defined in the select clause.

At the end the Map should look like the following figure:

The Map can also be represented as an Excel file. This is helpful for documentation requirements and discussions with different stakeholders when developing integrations.

Create a new Process called CSV to Database Importer. This process should include a File Connection to read the CSV file. Then use the previously created Map component and a Database Connection for the Oracle Database. It won’t explain every step in this process because there is nothing special. However I use “Extensions” for some configuration parameters of the used Connectors. It is useful when deploying the process to different environments.

Extension for CSV Import Directory (File Connector). I configured the Directory as Extension for this particular Connection. This configuration allows you to modify the input directory
during deployment

Extension for Oracle OE Database (Database Connector). I configured all connection settings as Extension for this particular Connection. This configuration allows you to modify all these values during deployment.

Next Step will be to deploy this process to the local Atom runtime. See the next chapter how to achieve this.

Deployment

Deploy the process to the local Atom runtime. Click Create Packaged Component in the process view.

Click Next: Add Details

Define Version number and click Create Packaged Component.

Click on Deploy

Choose the local Atom runtime

Click Next: Select Versions

Review all settings

Click Next: Review

Start deployment

Click Deploy

Navigate to Manage > Atom Management and select the appropriate environment. On the environment overview select Environment Extensions under Administration.

Select Connection Settings and choose the appropriate Connection from the dropdown list. Modify the values as needed for your environment. In the example the database connection settings will be prepared to access Oracle XE database.

Now it’s time to test the deployed process. See the next chapter.

Test the process

Now everything is prepared to execute the process. In order to do this add a csv file with test data to the configured input directory. When everything works fine you should see new rows in the table product_information like in the following figure:

Summary

In this example I explained how to import CSV data into an Oracle XE database. Some important considerations are JDBC driver configuration to use connection with service name instead of SID. Furthermore this example shows how to use SQL Lookup function in a Map. Finally I described how to prepare and configure connection configuration for different environments by using extensions.

References