SQL to Salesforce

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
srulymarkowitz
Posts: 3
Joined: Thu 08 Oct 2020 00:11

SQL to Salesforce

Post by srulymarkowitz » Thu 08 Oct 2020 00:16

Hi,

Having some trouble using external IDs.

I am doing 2 'pushes' from SQL to Salesforce
1) Accounts > Accounts
2) Donations > Opportunities

So first I Upsert the accounts, including mapping an external ID field to the account in SF. That works great.

Then I want to Upsert opportunities as well. But I need to link the Opportunity to the correct Salesforce Account. This is usually done with SF Account ID. In this case I don't know the SF account ID, but I do know the SF account external ID for each opportunity.

So how can I Upsert an Opportunity (child of Account) and reference the SF account ID, when all I have is the Account External ID.

From my research it might be done with the lookup component, but I am not understanding how to use it, and the documentation is a bit sparse.

Thanks in advance.

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

Re: SQL to Salesforce

Post by Shalex » Fri 09 Oct 2020 17:12

srulymarkowitz wrote:
Thu 08 Oct 2020 00:16
So how can I Upsert an Opportunity (child of Account) and reference the SF account ID, when all I have is the Account External ID.

From my research it might be done with the lookup component, but I am not understanding how to use it, and the documentation is a bit sparse.
That is correct. Refer to viewtopic.php?t=36569#p127580.

srulymarkowitz
Posts: 3
Joined: Thu 08 Oct 2020 00:11

Re: SQL to Salesforce

Post by srulymarkowitz » Fri 09 Oct 2020 17:31

I saw that.
But it is not very clear explanation and it is hard to pick out the useful info out of the concern about if the right product is being used.
Is there a proper tutorial on how to use that feature?

srulymarkowitz
Posts: 3
Joined: Thu 08 Oct 2020 00:11

Re: SQL to Salesforce

Post by srulymarkowitz » Mon 12 Oct 2020 21:00

For anyone else struggling with this- I figured it out.

• Create a Devart Salesforce Lookup
• In my case I want the Account ID - so I will Lookup object "Account"
• For lookup columns, select what you want to return. In this case I want the Account ID, so check "Id"
• The next section (referential contract) basically means match the column that contains external field of the parent (i.e. the account) that is in the child object, and match it with the actual Salesforce external column that contains that value.
So in my example I have a SQL "Account_ID" field which is mapped to Salesforce Account "Account_External_ID__c" field.
• Doing this will automatically create a new 'Id' column (sometimes referenced as "Devart Lookup.Id") which you can use as an input when mapping fields.
• So when creating my child object (Opportunities) I map"Id" to "Account ID". (Dont map it to ID of the Child, the ID of the child will automatically be generated upon record creation.

Post Reply