Using Oracle Data Integrator to load Salesforce data into text files

Oracle data integrator ODI 12c has made it extremely easy to load data into and out of Salesforce. There is a “technology” available for Salesforce connections which allows making connections to Salesforce very easily and “Reverse Engineer” Salesforce objects like “Accounts”, “Cases” etc.  The reverse engineered objects can then be used to create data models to be used to build interfaces and packages.

In this blog post, I will walk you through the steps involved in loading Salesforce data into text files. The ODI instance used is on a Windows server.

The exercise below requires the following pre-requisites.

  • Admin access to Salesforce
  • ODI 12C installed and configured

Step 1: Salesforce Security token

Get Salesforce Security Token by going to “Settings” under the user options as below. After clicking on the “Reset Security Token” button, an email will be sent to the registered email address.

Once this token is sent, please keep it handy for the next steps as we need to use it in setting up the Salesforce.

Step 2: Setting up Salesforce “Logical Schema” under Logical Architecture

Step 3: Setting up the corresponding “Physical Schema” under Physical Architecture

Start by right-clicking on the Salesforce technology and selecting “New Data Server”

Give the server a carefully thought-out name according to the projects naming conventions. In this example, its simply called “Salesforce”. Fill out the fields for username and password. Remember that password for Salesforce in any third party application is a combination of the Salesforce password and the token generated in the earlier step.

Under JDBC settings, select the Salesforce option from the dropdown menu. The  JDBC URL gets auto-populated. In the interest of keeping this exercise simple, remove additional settings by deleting all the text after See images below.

Once done, click save, and then test the connection. In some cases the pre-populated JDBC URL may have a typo and the connection test may fail. Make sure the URL is as below.


Upon successful testing, the following dialog should appear.

Step 4: Add new “Physical Schema” under the newly created Salesforce Data Server

Right click on the SF Data Server and select “New Physical Schema” which brings up the following screen

From the drop down menu under both Schema (Schema) and Schema (Work Schema) select SFORCE.

Click on the Context tab, and link this “Physical Schema” to the “Logical Schema”  for various ODI “Contexts”. In this example, all contexts have the logical schema connected to the same physical one.

Now that the building blocks are created, next steps would be to create “models” for various Salesforce objects, for example Accounts, opportunities, cases etc. This is done by reverse engineering those objects which is done in the “Designer” module of ODI.

Step 5: Create ODI Models for Salesforce objects

This is the most crucial step in the development process. Start by first creating a new model folder.  In this example, the folder is named SF_Test. Once the folder is created, right click and select “New Datastore” as shown below.

Under the Definition tab, fill out the details for the name, technology and logical schema with the appropriate details.

Next, click on the “Selective Reverse-Engineering” tab. Check the “Selective reverse-engineering” checkbox, which tells ODI that this model will be using selective reverse engineering. Then check all three check boxes so all available objects can be seen.  Use the toggle check boxes at the right edge to select and deselect all objects. The best practice is to “unselect’ all objects, and only manually select the ones needed for the project.

Once the desired objects are selected, click “Reverse Engineer” button at the top-left corner to start the process.

Use view data option in the models created to check if the data is being pulled from Salesforce and if it looks right. This is more of less for visual inspection. The following screenshot shows the “Contact” data.

This next image shows “Accounts” data out of Salesforce

Now all the building blocks are in place to use Salesforce as a data source in ODI. The next step will demonstrate an “interface” which loads account data into a text file. Given this is a fairly simple and common exercise, only screenshots are shown below. The process is pretty self-explanatory from here on.

We have now completed a practical hands-on exercise on loading Salesforce data into a flat file using Oracle Data Integrator. This is just a small example out of a number of complex things that can be done with ODI and Salesforce. Hope this serves as a good POC and help you with your projects.

Feel free to reach out to me for any follow up questions via my LinkedIn profile. Any feedback or suggestions would be greatly appreciated. Thanks for reading.

You may also like...

Leave a Reply

%d bloggers like this: