Creating a Mapping to perform an Update and Insert to an Oracle table with source data from Salesforce

As the post title says, in this post I have created a mapping to perform an update and insert to an Oracle table with source data from Salesforce. If you still have not created a Salesforce Data model, please refer to a previous post for reference.

Now to begin, in Designer, expand your project folder and create a new mapping

Enter a name for your Mapping

In Designer > Models accordion, select the necessary tables serving as your source and target object. In the Logical tab of your mapping, drag the tables. Example screenshot below is showing the source table which is in Salesforce and the target table which is in an Oracle database. I have added a filter in this example.

Still in the Logical tab, in the Properties > Target box, select 'Incremental Update' for the Integration Type.

One field in my Salesforce table has a data type of LONGTEXTAREA. Please refer to a previous post on how you can set ODI12c to handle such data type. 

In the Physical tab,  in the TARGET_GROUP highlighted, click on the target units. In Properties > Loading Knowledge Module, in the dropdown you either select <Default> or 'LKM SQL Multi-Connect'. 

Still in the Physical tab, click the target table. In Properties > Integration Knowledge Module, in the dropdown select the appropriate IKM. You can select the out of the box IKM: 'IKM SQL to SQL Incremental Update'. But this IKM will not work if one of the columns on your table has a CLOB data type. The IKM has a task to identify which fields are for insert or update. This involves comparing all fields in the where clause, however, this cannot be done in an SQL if one field has a CLOB data type. In this case, I have modified the IKM 'IKM SQL to SQL Incremental Update' so that only the SYSTEMMODSTAMP field will be checked by Oracle to determine if a record has been a changed. 

Save your work, execute and verify the data.

Creating a Mapping to do an Update & Insert to Salesforce with ODI12c

In this post, I created a mapping that will do an update and insert to a Salesforce table.

Now to begin, in Designer, expand your project folder and create a new mapping

Enter a name for your Mapping

In Designer > Models accordion, select the necessary tables serving as your source and target object. In the Logical tab of your mapping, drag the tables. Example screenshot below is showing the source table which is in an Oracle database and the target table which is in Salesforce. I have added a filter in this example.

Still in the Logical tab, in the Properties > Target box, select 'Incremental Update' for the Integration Type.

Still in the Logical tab, click the target table. In the Properties > Attributes box, make sure to untick the checkbox for the Salesforce Audit columns (The audit columns are the following: LastModifiedDate, CreatedById, IsDeleted, SystemModstamp, CreatedDate, LastModifiedById. In my example, I have also unchecked OwnerID and Name because they will be automatically assigned values by Salesforce during insert).

In the Physical tab,  in the TARGET_GROUP highlighted, click on the target units. In Properties > Loading Knowledge Module, in the dropdown you either select <Default> or 'LKM SQL Multi-Connect'. 

Still in the Physical tab, click the target table. In Properties > Integration Knowledge Module, in the dropdown select 'IKM SQL to SQL Incremental Update'. For the options, set FLOW_CONTROL and FLOW_TABLE_LOCATION to 'False' and 'STAGING', respectively. I have mentioned this in my previous post that Salesforce cannot be used as a staging location. Please refer to a previous post for more info on which IKMs you can use for Salesforce Integration with ODI12c.

Save your work, execute and verify the data.