Scenario: Using queries to extract data from a Salesforce database - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This scenario describes a four-component Job used to extract specific sets of data from parent and child objects in a Salesforce database.

Setting up the Job

  1. Drop two tSalesforceInput components and two tLogRow components onto the workspace.

  2. Connect each tSalesforceInput component to a tLogRow component using a Row > Main connection for each pair.

  3. Connect tSalesforceInput_1 to tSalesforceInput_2 using an OnSubjobOk connection.

Setting up the connection to the Salesforce server for the parent object

  1. Double-click tSalesforceInput_1 to set its Basic Settings in the Component tab.

  2. Enter the Salesforce WebService URL of the database you want to connect to in the corresponding field.

  3. Enter your authentication information in the corresponding Username and Password fields.

  4. Enter the desired query Timeout (milliseconds) limit.

Setting the query and the schema for the parent object

  1. Select the Module (salesforce object) you want to query.

  2. Select the Manual input of SOQL Query check box and enter your query scripts in the enabled Query field.

    The query scripts you enter should follow the SOQL syntax.

  3. Select Built-In as the Schema and click [...] next to Edit schema to open the schema editor.

    In this example, the IsWon and FiscalYear columns in the query are located in the Opportunity module specified. The Name column is in a linked module called Account. To return a column from a linked module the correct syntax is to enter the name of the linked module, followed by the period character, then the name of the column of interest. Hence, the query required in this example is:

    "SELECT IsWon, FiscalYear, Account.Name FROM Opportunity".

  4. Click the plus button to add a new column for the fields taken from the Name column in the Account module.

  5. Name this column Opportunity_Account_Name and click OK to save the changes.

    Warning

    To retrieve a column from a linked module, it is necessary to define the column in a particular manner in the Edit schema view. The correct syntax is: NameofCurrentModule_NameofLinkedModule_NameofColumnofInterest. Hence, in this example, the column must be named: Opportunity_Account_Name. If this syntax is not respected then the data from the linked table will not be returned.

Setting up the connection to the Salesforce server for the child object

  1. Double-click tSalesforceInput_2 to set its Basic settings in the Component tab.

  2. Enter the Salesforce WebService URL of the database you want to connect to in the corresponding field.

    The query scripts you enter must follow the SOQL syntax.

  3. Enter your authentication information in the corresponding Username and Password fields.

  4. Enter the desired query Timeout (milliseconds) limit.

Setting the query and the schema for the child object

  1. Select the Module (salesforce object) you want to query.

  2. Select the Manual input of SOQL Query check box and enter your query scripts in the enabled Query field.

    In this example we want to extract the Id and CaseNumber fields from the Case module as well as the Name fields from the Account module. The query is therefore: .

    "SELECT Id, CaseNumber, Account.Name FROM Case"

  3. Select Built-In as the Schema and click [...] next to Edit schema to open the schema editor.

  4. Click the plus button to add a new column for the fields taken from the Name column in the Account module.

  5. Name this column Case_Account_Name and click OK to save the changes.

Job execution

  1. Click each tLogRow component and set their component properties in the Basic settings view as desired.

    In this example, there is no need to modify the tLogRow settings.

  2. Press Ctrl+S to save your Job and press F6 to execute it.

    The results are displayed in the Run tab: