August 15, 2023

tSalesforceInput – Docs for ESB 6.x

  2. Talend for ESB Docs
  3. Docs 6.x
  4. tSalesforceInput – Docs for ESB 6.x

tSalesforceInput

Extracts data from Salesforce based on a query.

tSalesforceInput Standard properties

These properties are used to configure tSalesforceInput running
in the Standard Job framework.

The Standard
tSalesforceInput component belongs to the Business
and the Cloud families.

The component in this framework is generally available.

Basic settings

Property Type

Select the way the connection details
will be set.

  • Built-In: The connection details will be set
    locally for this component. You need to specify the values for all
    related connection properties manually.

  • Repository: The connection details stored
    centrally in Repository > Metadata will be reused by this component. You need to click
    the […] button next to it and in the pop-up
    Repository Content dialog box, select the
    connection details to be reused, and all related connection
    properties will be automatically filled in.

This property is not available when other connection component is selected
from the Connection Component drop-down list.

Connection Component

Select the component whose connection details
will be used to set up the connection.

Connection type

Select the type of the connection from the drop-down list, either Basic or OAuth.

  • Basic: select this option to access
    Salesforce.com by entering your user ID and password. With this option selected,
    you need to specify the following properties:

    • User Id: the ID of the user in
      Salesforce.

    • Password: the password associated
      with the user ID.

    • Security Key: the security
      token.

  • OAuth: select this option to access
    Salesforce.com by entering your consumer key and consumer secret. This way, your
    user name and password will not be exposed, but extra work is
    required:

    With this option selected, you need to specify the following
    properties:

    • Client Id and Client Secret: the OAuth consumer key and consumer
      secret, which are available in the OAuth Settings
      area of the Connected App that you have created at
      Salesforce.com.

    • Callback Host and Callback Port: the OAuth authentication
      callback URL. This URL (both host and port) is defined during the
      creation of a Connected App and will be shown in the OAuth
      Settings       area of the Connected App.

    • Token File: the path to the token
      file that stores the refresh token used to get the access token without
      authorization.

Module Name

Click the […] button next to the field and in the
dialog box displayed, select the module that will be used or select the Use custom object check box and specify the module name in the
Object Name field.

Schema and Edit schema

A schema is a row description. It defines the number of fields (columns) to
be processed and passed on to the next component. The schema is either Built-In or stored remotely in the Repository.

Click Edit schema to make changes to the schema.
If the current schema is of the Repository type, three
options are available:

  • View schema: choose this option to view the
    schema only.

  • Change to built-in property: choose this
    option to change the schema to Built-in for
    local changes.

  • Update repository connection: choose this
    option to change the schema stored in the repository and decide whether to propagate
    the changes to all the Jobs upon completion. If you just want to propagate the
    changes to the current Job, you can select No
    upon completion and choose this schema metadata again in the [Repository Content] window.

In this component the schema is related to the Module
selected.

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, otherwise the relationship query will not
work. The correct syntax is: NameofCurrentModule_Nameof-

LinkedModule_NameofColumnof-

Interest

This component offers the
advantage of the dynamic schema feature. This allows you to retrieve unknown columns
from source files or to copy batches of columns from a source without mapping each
column individually. For further information about dynamic schemas, see
Talend Studio

User Guide.

This dynamic schema
feature is designed for the purpose of retrieving unknown columns of a table and is
recommended to be used for this purpose only; it is not recommended for the use of
creating tables.

Query Mode

Select the query mode from the drop-down list, either
Query or Bulk.

  • Query: the ordinary query.

  • Bulk: the bulk query used to query
    large data sets efficiently and reduce the number of API requests.

Condition

Enter the query used to select the data to be extracted between
double quotation marks, for example, “name=’Talend'” or
“name like ‘%talend_user%'”.

This field is not available when the Manual Query check box is selected.

Manual Query

Select this check box and in the Full SOQL query string field displayed, enter the full SOQL
(Salesforce Object Query Language) statement used to select the data to be
retrieved between double quotation marks. For more information about the SOQL,
see Salesforce Object Query
Language (SOQL).
Guess schema

Click this button to generate the schema columns based on the query specified
in the Full SOQL query string field.

This button is available only when the Manual Query check box is
selected.

Note that there are some limitations you
should be aware of when using this feature. Please refer to the Know Limitations section in
Release Notes for more detailed information.
Guess query

Click this button to generate the query in the Full SOQL query
string field based on the defined module and schema.

For more information about how to set the module and schema correctly when
generating the SOQL queries, see How to set schema for the guess query feature of tSalesforceInput.

This button is available only when the Manual Query check box is
selected.

Note that there are some limitations you
should be aware of when using this feature. Please refer to the Know Limitations section in
Release Notes for more detailed information.

Include deleted records

Select this check box to query all the records, including the
deleted ones.

This check box is available only when Query is selected from the Query Mode drop-down list.

Advanced settings

Salesforce URL

Enter the Webservice URL required to connect to
the Salesforce database.

Need compression

Select this check box to activate SOAP message compression, which can result in increased
performance levels.

Trace HTTP message

Select this check box to output the HTTP interactions on the console.

This check box is available only when Bulk is selected from the Query Mode drop-down list.

Use HTTP Chunked

Select this check box to use the HTTP chunked data transfer mechanism.

This check box is available only when Query is selected from the Query Mode drop-down list.

Client Id

Enter the ID of the real user to differentiate between those who use the same account and
password to access the Salesforce website.

This field is available only when Query is selected from the Query
Mode drop-down list.

Timeout (milliseconds)

Enter the intended number of query timeout in Salesforce.com.

Use Proxy

Select this check box to use a proxy server, and in the Host, Port, User Id, and Password fields displayed, specify
the connection parameters of the proxy server.

Batch Size

Enter the number of registrations in each processed batch.

This field is available only when Query is selected from the Query
Mode drop-down list.

Normalize Delimiter

Enter the characters, strings or regular expressions used to
normalize the data that is collected by queries set on different hierarchical
Salesforce objects.

This field is available only when Query is selected from the Query
Mode drop-down list.

Column Name Delimiter

Enter the characters, strings or regular expressions used to
separate the name of the parent object from the name of the child object when
you use a query on the hierarchical relations among the different Salesforce
objects.

This field is available only when Query is selected from the Query
Mode drop-down list.

Enable PK Chunking

Select this check box to enable PK (Primary Key, i.e., the object’s record ID)
chunking when extracting large amounts of records or when the query
consistently times out. In the Chunk size field
displayed, specify the number of records within the ID boundaries for each
chunk. For more information, see Use PK Chunking to Extract Large Data Sets
from Salesforce.

This check box is available only when Bulk is selected
from the Query Mode drop-down list.

tStatCatcher Statistics

Select this check box to gather the Job processing metadata at the Job level
as well as at each component level.

Global Variables

Global Variables

NB_LINE: the number of rows read by an input component or
transferred to an output component. This is an After variable and it returns an
integer.

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.

Usage

Usage rule

This component is usually used as a start component of a Job or Subjob and it
always needs an output link.

How to set schema for the guess query feature of tSalesforceInput

tSalesforceInput allows you to generate SOQL queries, with
both standard objects and fields and custom objects and fields, based on the defined module
and schema. This section demonstrates how to set the module and schema when generating an SOQL
query.

There are two types of SOQL queries: simple query and relationship query.

Simple query retrieves data from only one object. For more information about how to set the
module and schema for generating simple queries, see How to set schema for generating the simple query.

Relationship query retrieves data from more than a single type of object, including
child-to-parent and parent-to-child relationship queries.

For more information about how to set the module and schema for generating child-to-parent
relationship queries, see How to set schema for generating the child-to-parent relationship query.

For more information about how to set the module and schema for generating parent-to-child
relationship queries, see How to set schema for generating the parent-to-child relationship query.

Note that before setting the module name and schema columns for generating the relationship
query in the Basic settings view of
tSalesforceInput, you need to first identify the relationship type
(child-to-parent or parent-to-child) of the query to be generated, because the methods of
setting the schema column names for these two kinds of relationship queries are somewhat
different. The main difference between the child-to-parent and parent-to-child relationship
queries is that the parent-to-child relationship query is specified by using the subquery
enclosed in parentheses, while the child-to-parent relationship query is not. For more
information about the relationship queries in SOQL and how to identify the relationship
queries, see Relationship Queries.

How to set schema for generating the simple query

This section demonstrates how to set the module name and schema columns for generating
the simple query.

The following two simple query examples will be used for demonstration purpose in the
following steps.

  • SELECT Id, Name, BillingCity FROM Account, a simple query with
    standard object and fields, and

  • SELECT Name__c, LastName__c FROM Mother__c, a simple query with
    custom object and fields.

  1. Set the module name with the name of the object specified in the
    FROM clause, Account and
    Mother__c for above examples.
  2. Create a column for each field in the field list (separated by commas) after
    SELECT in the schema dialog box and set the column name
    with the field name.

    For the first example, you need to create three columns Id,
    Name, and BillingCity for the three
    fields.

    For the second example, you need to create two columns
    Name__c and LastName__c for the two
    fields.

How to set schema for generating the child-to-parent relationship query

This section demonstrates how to set the module name and schema columns for
generating a child-to-parent relationship query.

The following two child-to-parent relationship query examples will be used for
demonstration purpose in the following steps.

  • SELECT Name, Account.Name, Account.Owner.Name FROM Contact, a
    child-to-parent relationship query with standard object and fields, and

  • SELECT Id, FirstName__c, MotherOfDaughter__r.FirstName__c FROM
    Daughter__c    , a child-to-parent relationship query with custom
    object and fields.

    Note that here you must use the relationship name with __r
    instead of __c. For more information, see Understanding Relationship Names, Custom
    Objects, and Custom Fields    .

  1. Set the module name with the name of the object specified in the
    FROM clause, Contact and
    Daughter__c in above examples.
  2. Create a column for each field in the field list (separated by commas) after
    SELECT in the schema dialog box.

    For the first example, you need to create three columns for the three fields
    Name, Account.Name, and
    Account.Owner.Name.

    For the second example, you need to create three columns for the three fields
    Id, FirstName__c, and
    MotherOfDaughter__r.FirstName__c.

  3. Set the name of each column with the name of each field and replace all dots in
    the column name with underscore characters.

    For the first example, the names of the three columns are set to
    Name, Account_Name, and
    Account_Owner_Name.

    For the second example, the names of the three columns are set to
    Id, FirstName__c, and
    MotherOfDaughter__r_FirstName__c.

  4. Set the type of each column.

    The schema for the first example should be set like this:

    child_to_parent_query_schema.png

    And the schema for the second example should be set like this:

    child_to_parent_query_schema_custom.png

    Note that the underscore character ‘_’ is
    used as a separator between the relationship name and the field name in
    Talend
    schema, so only the underscore character ‘_’ that goes after ‘__r’ or ‘__c’
    will be replaced by the dot character ‘.’ when generating the query. If the
    underscore character ‘_’ is a part of any custom name in the schema, for
    example, Contact_custom_field__c, which should be
    Contact.custom_field__c in the query, you need to
    replace ‘_’ in the generated query with ‘.’ manually.

How to set schema for generating the parent-to-child relationship query

This section demonstrates how to set the module name and schema columns for
generating a parent-to-child relationship query.

The following two parent-to-child relationship query examples will be used for
demonstration purpose in the following steps.

  • SELECT Name, Owner.Name (SELECT CreatedBy.Name FROM Notes) FROM
    Account    , a parent-to-child relationship query with standard object
    and fields, and

  • SELECT LastName__c, (SELECT FirstName__c FROM Daughters__r) FROM
    Mother__c    , a parent-to-child relationship query with custom object
    and fields.

    Note that here you must use the relationship name with __r
    instead of __c. For more information, see Understanding Relationship Names, Custom
    Objects, and Custom Fields    .

  1. Set the module name with the name of the object specified in the outer query
    FROM clause, Account and
    Mother__c in above examples.
  2. Create a column for each field (inclulding the fields in subquery) after
    SELECT in the schema dialog box.

    For the first example, you need to create three columns for the three fields,
    including two fields Name and Owner.Name
    after the outer SELECT, and one field
    CreatedBy.Name after the subquery
    SELECT.

    For the second example, you need to create two columns for the two fields,
    including the field LastName__c after the outer
    SELECT, and the field FirstName__c
    after the subquery SELECT.

  3. For the fields in the outer SELECT clause, which are outside
    parentheses, set the name of each column with the name of each field and replace
    all dots in the column name with underscore characters.

    For the first example, the column names for the two fields
    Name and Owner.Name in the
    outer SELECT clause are set to Name
    and Owner_Name.

    For the second example, there is no dot in the field name, so the column name
    is same as the field name.

  4. For the fields in the subquery SELECT, construct the column
    names using the pattern <$XXX>_records_<$YYY>, where
    <$XXX> corresponds to the name of the object specified
    in the subquery FROM clause, and <$YYY>
    will be the field name with all dots replaced by underscore characters.

    For the first example, the column name for the field
    CreatedBy.Name in the subquery is set to
    Notes_records_CreatedBy_Name.

    For the second example, the column name for the field
    FirstName__c in the subquery is set to
    Daughters__r_records_FirstName__c.

  5. Set the type of each column.

    The schema for the first example should be set like this:

    parent_to_child_query_schema.png

    And the schema for the second example should be set like this:

    parent_to_child_query_schema_custom.png

    Note that the underscore character ‘_’ is
    used as a separator between the relationship name and the field name in
    Talend
    schema, so only the underscore character ‘_’ that goes after ‘__r’ or ‘__c’
    will be replaced by the dot character ‘.’ when generating the query. If the
    underscore character ‘_’ is a part of any custom name in the schema, for
    example, Contact_custom_field__c, which should be
    Contact.custom_field__c in the query, you need to
    replace ‘_’ in the generated query with ‘.’ manually.

Scenario: Extracting data from a Salesforce database using the SOQL query

This scenario describes a two-component Job used to extract a specific set of data from an
object in a Salesforce database.

tSalesforceInputScenario1.png

Setting up the Job

  1. Create a new Job and add a tSalesforceInput
    component and a tLogRow component by typing
    their names on the design workspace or dropping them from the Palette.
  2. Connect the tSalesforceInput component to the
    tLogRow component using a Row > Main
    connection.

Configuring the components

  1. Double-click the tSalesforceInput component
    to open its Basic settings view.

    Use_Case_tSalesforceInput_1_Properties.png

  2. In the User Id, Password and Security Key
    fields, enter the user authentication information required to access
    Salesforce.
  3. Click the […] button next to the Module Name field and in the pop-up dialog box,
    select the object you want to access. In this example, it is Opportunity.
  4. Click the […] button next to Edit schema to open the schema dialog box.

    tSalesforceInput_Schema_Editor1.png

  5. Remove all columns except Id, Name, IsWon, and
    Fiscal Year. Then add another column
    Opportunity_Account_Name of String
    type.

    Note that to retrieve a column from a linked object, it is necessary to define
    the name of the column in a particular manner in the schema editor. The correct
    syntax is
    NameofCurrentObject_NameofLinkedObject_NameofColumnofInterest.
    Hence, in this example, to retrieve the Name
    column in the Account object, the name of the
    fifth column must be Opportunity_Account_Name. If this syntax is not respected, the data
    from the linked object will not be returned.
    Click OK to save the changes and close the
    schema dialog box.
  6. Select the Manual Query check box and in the
    Full SOQL query string field displayed,
    enter your SOQL statement used to search the data to be retrieved. In this
    example, the statement is as follows:

    Note that to return a column from a linked object, the correct syntax of the
    column name in a SOQL statement is
    NameofCurrentObject.NameofColumnofInterest. Hence, in this
    example, the fifth column name in the SOQL statement is Account.Name.
  7. Double-click the tLogRow component to open
    its Basic settings view.

    components-tsalesforceinput_s1_tlogrow.png

  8. In the Mode area, select Table (print values in cells of a table) for better
    readability of the result.

Executing the Job

  1. Press Ctrl + S to save your Job.
  2. Press F6 to execute your Job.

    tSalesforceInput_RunTab.png

    As shown above, the data in the Opportunity
    object is selected and displayed on the console.
Document get from Talend https://help.talend.com
Thank you for watching.
Docs 6.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x