SSIS Salesforce 1.6 External ID Lookup Issue

Discussion of issues, suggestions and bugs of Devart SSIS Data Flow Components, our product line for building SSIS-based ETL solutions, performing data access to popular cloud applications and databases.
Post Reply
rrossow_lmi
Posts: 2
Joined: Thu 08 Feb 2018 19:16

SSIS Salesforce 1.6 External ID Lookup Issue

Post by rrossow_lmi » Thu 08 Feb 2018 19:39

I'm working with the SSIS Salesforce 1.6 components. The documentation for "Loading Related Data Using External ID" references a "Custom Properties" in the "Input and Output Properties" tab using the "Advanced Editor for Devart Salesforce Destination". I only see the "Common Properties" and "Data Type Properties" in the advanced editor in SQL Server data tools 2015 or visual studio 2017. What have I missed?

Info:
* Connecting to a custom salesforce object
* Performing an upsert
* Connection is keying on a ExternalID within the custom object.
* Attempting to link custom object to account.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Salesforce 1.6 External ID Lookup Issue

Post by Shalex » Fri 09 Feb 2018 10:59

rrossow_lmi wrote:I'm working with the SSIS Salesforce 1.6 components. The documentation for "Loading Related Data Using External ID" references a "Custom Properties" in the "Input and Output Properties" tab using the "Advanced Editor for Devart Salesforce Destination".
The documentation, which covers these topics, corresponds to a product with name "dotConnect for Salesforce Professional".

You are using a different product - "SSIS Data Flow Components for Salesforce" (which is newer than "dotConnect for Salesforce"), aren't you? We didn't implement the "Custom Properties" section of the "Devart Salesforce Destination" component in "SSIS Data Flow Components for Salesforce" because it, actually, duplicates an existing functionality available via "Devart Salesforce Lookup" and standard "Cache Transform" components.

There are the following ways to use the ExternalId and CachedId features:

* ExternalId
after data are loaded into master (Account), employ our "Devart Salesforce Lookup" component when inserting data into ClientObject_c:
"some source" -> "Devart Salesforce Lookup" -> "Devart Salesforce Destination"
Lookup Object = Account
Lookup Columns = Id
Input Colums = clientid (column in your source database table)
Reference Columns = client__c (column in your Account)

* CachedId
after data are loaded into master (Account), employ a standard component "Cache Transform" in a an additional parallel data flow when inserting data into ClientObject_c
1st data flow: "Devart Salesforce Source" -> "Cache Transform"
2nd data flow: "some source" -> "Lookup" -> "Devart Salesforce Destination"
Refer to https://docs.microsoft.com/en-us/sql/in ... -transform.

Does this help?

rrossow_lmi
Posts: 2
Joined: Thu 08 Feb 2018 19:16

Re: SSIS Salesforce 1.6 External ID Lookup Issue

Post by rrossow_lmi » Fri 09 Feb 2018 13:53

Thank you for the quick reply,
The suggestion and method provided does not help and is for a different product than what is being used.

Product Being Used: https://www.devart.com/ssis/salesforce.html (Am I in the correct forum?)
Image of documentation being referenced:
Image

Image of what is being encountered within SSIS
Image

Performing a lookup is a way to get around the Custom Properties section not showing. It also increases Salesforce API usage and the reason to use External ID's as the lookup for them can be done without a separate lookup call.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Salesforce 1.6 External ID Lookup Issue

Post by Shalex » Fri 16 Feb 2018 12:21

rrossow_lmi wrote:The suggestion and method provided does not help and is for a different product than what is being used.
The suggestion is for the product "SSIS Data Flow Components for Salesforce".
rrossow_lmi wrote:Product Being Used: https://www.devart.com/ssis/salesforce.html (Am I in the correct forum?)
Image of documentation being referenced: [...]
Thank you for pointing to it. We will remove this article from SSISDataFlow.chm.
rrossow_lmi wrote:Performing a lookup is a way to get around the Custom Properties section not showing. It also increases Salesforce API usage and the reason to use External ID's as the lookup for them can be done without a separate lookup call.
This workaround uses the same number of API calls like the approach via Custom Properties:
* CachedId
after data are loaded into master (Account), employ standard components "Cache Transform" and "Lookup" in two parallel data flows when inserting data into ClientObject_c
1st data flow: "Devart Salesforce Source" -> "Cache Transform"
2nd data flow: "some source" -> "Lookup" -> "Devart Salesforce Destination"
Refer to https://docs.microsoft.com/en-us/sql/in ... -transform.

Post Reply