Replicating Salesforce Archived Data

[juandavidc]

Hi,

We have the TASK table that has the auto archiving process from Salesforce. We want to replicate all data in the table and when performing a full load from Salesforce Incremental + SOAP Api, Qlik does not recognize or see the data that is available but marked as 'Archived: IsArchive=1'. Full Load replicated 11+ million rows, however there are over 40 million rows in the table.

I've searched and haven't found anything. Anyone ideas or work around for this using Qlik Replicate? I've seen from my search Informatica has an option that can be enabled to extract this archived data. Similar to the functionality of Workbench.

Qlik Replicate 

[john_wang]

Hello @juandavidc ,

Thanks for you reaching out.

Per my researching the current Replicate versions does not support the passthrough filter for Salesforce source Endpoint, that's why we cannot get the additional rows. You may  open an idea .

My steps: Let's say we can query the 'IsDeleted' rows, or 'isArchived' rows in some 3rd party tools, eg:

So I tried to add the filter to Replicate passthrough filter as below:

At beginning I hope it works as salesforce link isArchived Event/Task Field . However from the task runtime log files, the filter or conditional expression does not work .  Because Salesforce endpoint is not as same as a RDBMS one, the passthrough expression was ignored (although no error or warning popped up).

Regards,

John.

[NakulanR]

Hi,

Just wondering if there has been any further progress with this. There's no mention of the IsArchived field in the Replicate user guide, so I would assume that the limitation is on the Salesforce APIs. Would exclusively using one of the Bulk or SOAP APIs be able to resolve the issue? Or is there an internal parameter that could be enabled on either of the Salesforce endpoints which would allow visibility/filtering on the IsArchived field?

Regards,

Nak

[john_wang]

Hello Nak @NakulanR ,

Thanks for reaching out!

There are some changes (below sample is based on Replicate 2023.5):

1- The Boolean fields are visible in GUI and can be replicated to target side now

      (In the old versions these fields are invisible).  A sample screen shot:

2- However in this version the "Fullload Passthru Filter" is ignored still (see above screen shot), Replicate does not replicate the rows with "IsDeleted = 1" and/or "IsArchived= 1". This is implicit Query Behavior and I'm afraid there is no way to change it at present. Please raise Feature Request if you need it.

      Comparison between SOQL include "Deleted and archived records" and Replicate SQL, SOQL shows the IsDeleted row (the latest line of the result set):

From Replicate endpoint server log file we see the last row is implicitly filtered out (we got 5 lines instead of 6 lines data):

qtp391254696-1512 2023-09-04 09:33:38.807 [sourceunload ] [VERBOSE] [] Unload 'Task' using SOAP API. Executing SELECT query: 'SELECT Id, Subject, IsDeleted, IsArchived FROM Task'
qtp391254696-1512 2023-09-04 09:33:38.807 [sourceunload ] [VERBOSE] [] ENTER: SoapAPIConnection.querySF: SELECT Id, Subject, IsDeleted, IsArchived FROM Task
qtp391254696-1512 2023-09-04 09:33:40.400 [sourceunload ] [TRACE ] [] Query returned 5 objects, query: SELECT Id, Subject, IsDeleted, IsArchived FROM Task
qtp391254696-1512 2023-09-04 09:33:40.400 [sourceunload ] [VERBOSE] [] Query executed: SELECT Id, Subject, IsDeleted, IsArchived FROM Task
qtp391254696-1512 2023-09-04 09:33:40.400 [sourceunload ] [VERBOSE] [] The query result 5 record(s)
qtp391254696-1512 2023-09-04 09:33:40.400 [sourceunload ] [TRACE ] [] Table 'Task': Sent 5 records.

BTW, the behavior is irrelevant to Bulk or SOAP APIs versions etc.

Regards,

John.