OWB Integration with Salesforce.com Data: Sometimes The Old Ways are the Best Ways

A request that we get frequently enough to comment on is, "How do I use OWB to move data between Salesforce.com and my Oracle database?" Data warehouse customers in particular seem to be running into this one.

Usually, the person asking expects that some kind of web service call will be the solution of choice. Perhaps it would be, in some arcane cases. There are also ODBC drivers for Salesforce.com, which OWB could connect to with similar results to other ODBC data sources. (Of course, Oracle support won't help with those third-party components.)


However, bulk data movement is the most common case, and there are tried and true ways of doing that without the latest SOA secret sauce.

The moving parts of the simple-enough solution are:

  • The Salesforce.com "Apex Data Loader" utility can extract the results of one or several queries into CSV flat files, or upload bulk changes to Salesforce.com, and can be run from the command line. You can find more about Apex Data Loader here:

    http://wiki.apexdevnet.com/index.php/Apex_Data_Loader_9.0_Apex_Data_Loader

    http://wiki.apexdevnet.com/index.php/Using_Data_Loader_from_the_command_line


  • The user-defined process activity in OWB process flows lets you call a command-line utility such as the Apex Data Loader. You can read more about the user-defined process activity here:

    http://download.oracle.com/docs/cd/B28359_01/owb.111/b31278/ref_processflows.htm#insertedID22


  • For extraction, you can create a process flow activity that executes the Apex Data Loader to extract data from Salesforce into a flat file, then loads the flat file contents into your target DB. For loading, do the reverse-- extract data from Oracle to flat files, then run Apex Data Loader to upload.

So ultimately this is no different from moving data back and forth using flat files with any other foreign system, something OWB users have no doubt done since time immemorial.

If you apply this technique in a real deployment, let us know and we'll spotlight you here on the blog.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today