function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BMIZZYBMIZZY 

Join Queries in SSIS

I've been working with several integration models, and have been quite successful in doing almost everything we need through SSIS.  HOWEVER, I've come to a huge brain-free area that I can't seem to think past.  I'm sure it's an easy fix, but I just can't resolve it...

 

If I do a join SOQL query, pulling back information from two or more tables, how do I pass that information on?

 

Example from some SFDC documentation:

 

SELECT Name, (SELECT LastName FROM Contacts) FROM Account

 If I run that query, I'll get back one sObject of type Account.  I can't assign anything to an sObject of type Contacts, because that's not what's coming back.

 

I'd really, really like to be able to write my queries in this fashion, but just can't figure out how to pass the data along.  Once I get the results back, how can I assign it to an sObject for actual use?

 

Someone, please... Help me get past this blindspot! 

Thanks.

BritishBoyinDCBritishBoyinDC

This blog post I wrote might help, but they key idea is this:

 

 

sid = "Select OpportunityId, Opportunity.Amount, Opportunity.Name from OpportunityContactRole

Set qr = g_sfApi.Query(sid, False)

For Each v In qr
Set s = v
Set sop = s.GetJoinResults("Opportunity")
Range("A1").Cells(irow, 2 + (ci + ci - 1)).Value = s("OpportunityId")
Range("A1").Cells(irow, 2 + (ci * 2)).Value = sop("Name") & " " & sop("Amount")
ci = ci + 1
Next



Basically, s is an sObject4 (this ony works with the new version 4.0 of the office toolkit), and then sop is another sObject4. Once you have the results assigned to s, you can then use the GetJoinResults method to access related records (in your case the Contacts)