This section discusses some of the features and restrictions that are specific to how the component supports JOINs.

If possible, the component attempts to perform JOINs server-side. JOINs that Salesforce cannot process are performed client-side when SupportEnhancedSQL is set to true.

The CData SSIS Components for Salesforce supports server-side JOINs based on Salesforce Object Query Language (SOQL). The component supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. JOIN queries in Salesforce are based on the relationships among Salesforce objects.

Relationship Queries

Salesforce objects can be linked using relationships. The standard Salesforce objects have predefined relationships. You can define relationships for your custom objects.

Parent to Child Relationships

Salesforce relationships are directional and are of the following types: one-to-many (parent to child) or many-to-one (child to parent). Since the relationships are directional, the order in which the tables are included in the query determines the path of relationship traversal.

The following query shows a simple parent-to-child JOIN query. This query returns all Accounts and the first and last name of each Contact associated with that Account.

SELECT Contact.FirstName, Account.Name
FROM Account LEFT JOIN Contact ON Account.Id = Contact.AccountId

Polymorphic Relationships

Salesforce relationships can be polymorphic. That is, a given relationship on a field can refer to more than one type of entity. For example, the Task entity contains a Who relationship, which, by default, may refer to a Contact or Lead.

The following query shows a JOIN based on a polymorphic relationship. This query returns all contacts and task information that relate to a contact.

SELECT Task.Subject, Contact.Name 
    FROM Contact LEFT JOIN Task ON Task.WhoId = Contact.Id

Custom Relationships

You can specify a JOIN condition that is a custom relationship. The following query retrieves the names of all Account records and the first names of all Contacts that match the specified JOIN condition:

SELECT Contact.Firstname, Account.Name 
FROM Account 
JOIN Contact 
ON Account.MyCustomColumn__c = Contact.Id

Server-Side JOIN Syntax

The component internally analyzes the Salesforce objects' relationships and tries to resolve and translate as many SQL JOINs as possible into Salesforce relationship queries for faster and better performance.

You can use the syntax detailed below to execute JOINs on Salesforce objects that are processed by the Salesforce servers.

The following query returns the first names of all the Contacts in the organization and for each Contact the name of the parent Account associated with that Contact.

SELECT Contact.Firstname, Account.Name
FROM Contact LEFT JOIN Account ON Contact.AccountId = Account.Id

Salesforce supports INNER JOINs. The following query retrieves all Account records that are associated with an Opportunity:

SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDate 
FROM Account
INNER JOIN Opportunity 
ON Account.Id = Opportunity.AccountId