Querying Salesforce Deleted Records Using ODI 12c (Doc ID 2270540.1)

Last updated on APRIL 20, 2023

Applies to:

Goal

Following approaches have been tried to query the records with IsDeleted=true in a Salesforce table, using Oracle Data Integrator (ODI) 12.2.1.2.6:

• Create a filter with "IsDeleted = true" under Salesforce Datastore and using it in a Mapping as source to write to a target file.
• Right-click on the Salesforce Datastore and click "Data". Then query using "Select * from table_name WHERE IsDeleted = true".
• Use the OdiSqlUnload tool to dump the data into file.

However, none of them allowed to view the records having "IsDeleted=true".

Is there any way to query these deleted records?

Note, following URLs for the Salesforce Data Server have been used in the above attempts:

• jdbc:weblogic:sforce://test.salesforce.com;DatabaseName=SFDC;ConfigOptions=(AuditColumns=auditOnly;UppercaseIdentifiers=true;CustomSuffix=include;KeywordConflictSuffix=TAB;MapSystemColumnNames=0;NumberFieldMapping=alwaysDouble)
• jdbc:weblogic:sforce://test.salesforce.com;DatabaseName=SFDC;ConfigOptions=(AuditColumns=all;MapSystemColumnNames=0;UppercaseIdentifiers=true;CustomSuffix=strip;KeywordConflictSuffix=;NumberFieldMapping=emulateInteger;)

In all the above cases, customer is able to:

• View or get the data for all other system columns except "IsDeleted",
• Query the deleted records in the Salesforce database using Force.com explorer.
• Query the active records (the ones having IsDeleted=false).

Solution