- Define the data to read from Salesforce as follows:
- salesforce connection - either click the drop-down arrow and select an existing connection, or click (create new) to create a new connection.
- object name - the name of the table to be imported.
- object fields - add or remove fields to import for the selected object.
- where clause - optional. You can add SOQL predicate clauses to the to filter the data at the source.
- After selecting the fields to import, you can change the alias to use as a field in the data-flow.
In order to load data incrementally (changes and additions) to objects, the object to synchronize should have the systemmodstamp column. This column is automatically updated whenever a user or an automated process updates a record. Use the following condition in the where clause field with a variable:
SystemModstamp > $lastsysmod
You can use the last successful submission timestamp for the package as you can see in the example below as a value for the variable, or use ExecuteSqlDatetime function to get the last SystemModstamp in your destination database table.
lastsysmod = CASE WHEN (COALESCE($_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP,'')=='') THEN '1900-01-01T00:00:00Z' ELSE $_PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP END
In order to store additions or changes in your database destination, make sure to mark the id column as key and change the operation type to "merge":