tSalesforceOutput - 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

tSalesforceOutput Properties

Component family

Business/Cloud

 

Function

tSalesforceoutput writes in an object of a Salesforce database via the relevant Web service.

Purpose

Allows to write data into a Salesforce DB.

Basic settings

Property type

Either Built-in or Repository.

Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.

 

 

Built-in: No property data is stored centrally.

 

 

Repository: Select the Repository file where Properties are stored. The fields that follow are pre-filled in using fetched data.

 

Use an existing connection

Select this check box and in the Component List click the relevant connection component to reuse the connection details you already defined.

Note

When a Job contains the parent Job and the child Job, Component List presents only the connection components in the same Job level.

 

Login Type

Two options are available:

Basic: select this option to log in to Salesforce.com by entering your Username/Password on tSalesforceConnection.

OAuth2: select this option to access Salesforce.com by entering your Consumer key/Consumer Secret on tSalesforceConnection. This way, your Username/Password will not be exposed to tSalesforceConnection but extra work is required:

 

Salesforce Webservice URL

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

 

Salesforce Version

Enter the Salesforce version you are using.

 

Username and Password

Enter your Web service authentication details.

To enter the password, click the [...] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.

 

Consumer Key and Consumer Secret

Enter your OAuth authentication details. Such information is available in the OAuth Settings area of the Connected App that you have created at Salesforce.com.

To enter the consumer secret, click the [...] button next to the consumer secret field, and then in the pop-up dialog box enter the consumer secret between double quotes and click OK to save the settings.

For what a Connected App is, see Connected Apps. For how to create a Connected App, see Defining Remote Access Applications.

 

Callback Host and Callback Port

Enter your 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

Enter the token file name. It stores the refresh token that is used to get the access token without authorization.

 

Timeout (milliseconds)

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

 

Action

You can do any of the following operations on the data of the Salesforce object:

Insert: insert data.

Update: update data.

Delete: delete data.

Upsert: update and insert data.

 

Upsert Key Column

Specify the key column for the upsert operation.

Available when Upsert is selected from the Action list.

 

Module

Select the relevant module in the list.

Note

If you select the Use Custom module option, you display the Custom Module Name field where you can enter the name of the module you want to connect to.

 

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.

Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.

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.

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.

Advanced settings

Extended Output

This check box is selected by default. It allows to transfer output data in batches. You can specify the number of lines per batch in the Rows to commit field.

 

Die on error

This check box is selected by default. Clear the check box to skip the row on error and complete the process for error-free rows. If needed, you can retrieve the rows on error via a Row > Reject link.

Note

The Reject link is available only when you have deselected the Extended Output and Die on error check boxes.

 

Error logging file

If you want to create a file that holds all error logs, click the three-dot button next to this field and browse to the specified file to set its access path and its name.

 

Use Socks Proxy

Select this check box if you want to use a proxy server. Once selected, you need enter the connection parameters that are the host, the port, the username and the passerword of the Proxy you need to use.

 

Ignore NULL fields values

Select this check box to ignore NULL values in Update or Upsert mode.

 

Use Soap Compression

Select this check box to activate the SOAP compression.

Note

The compression of SOAP messages optimizes system performance.

 

Retrieve inserted ID

Select this check box to allow Salesforce.com to return the salesforce ID produced for a new row that is to be inserted. The ID column is added to the processed data schema in Salesforce.com.

Note

This option is available only when you have chosen insert action yet not in batch mode, not in the Extended Output option.

 

tStatCatcher Statistics

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

 

Client ID

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

 

Relationship mapping for upsert (for upsert action only)

Click the [+] button to add lines as needed and specify the external ID fields in the input flow, the lookup relationship fields in the upsert module, the lookup module as well as the external id fields in the lookup module.

Column name of Talend schema: external ID field in the input flow.

Field name: the name of the lookup field. It refers to the lookup field of the module specified in the Module list in the Basic settings view. This column needs to be specified when there are NULL input values and the Ignore NULL fields values check box is cleared.

Lookup field name: lookup relationship fields in the upsert module.

Module name: name of the lookup module.

External id name: external ID field in the lookup module.

Note

  • Column name of Talend schema refers to the fields in the schema of the component preceding tSalesforceOutput. Such columns are intended to match against the external id fields specified in the External id name column, which are the fields of the lookup module specified in the Module name column.

  • Lookup field name refers to the lookup relationship fields of the module selected from the Module list in the Basic settings view. They are intended to establish relationship with the lookup module specified in the Module name column.

  • For how to define the lookup relationship fields and how to provide their correct names in the Lookup field name field, go to the Salesforce website and launch the Salesforce Data Loader application for proper actions and information.

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.

NB_SUCCESS: the number of rows successfully processed. This is an After variable and it returns an integer.

NB_REJECT: the number of rows rejected. 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

Used as an output component. An Input component is required.

Log4j

If you are using a subscription-based version of the Studio, the activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User Guide.

For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html.

Limitation

n/a

Scenario 1: Deleting data from the Account object

This scenario describes a two-component Job that removes an entry from the Account object.

Dragging and dropping as well as connecting the components

  1. Drop tSalesforceInput and tSalesforceOutput from the Palette onto the design workspace.

  2. Connect the two components together using a Row > Main link.

Configuring the components

Querying the content to be deleted

  1. Double-click tSalesforceInput to display its Basic settings view and define the component properties.

  2. From the Property Type list, select Repository if you have already stored the connection to the salesforce server in the Metadata node of the Repository tree view. The property fields that follow are automatically filled in. If you have not defined the server connection locally in the Repository, fill in the details manually after selecting Built-in from the Property Type list.

    For more information about how to create the salesforce metadata, see Talend Studio User Guide.

  3. In the Salesforce WebService URL field, use the default URL of the Salesforce Web service or enter the URL you want to access or select the Use an existing connection check box to use an established connection.

  4. In the Username and Password fields, enter your login and password for the Web service.

  5. Type in your intended query timeout in the Timeout (milliseconds) field. In this example, use the default number.

  6. From the Module list, select the object you want to access, Account in this example.

  7. From the Schema list, select Repository and then click the three-dot button to open a dialog box where you can select the repository schema you want to use for this component. If you have not defined your schema locally in the metadata, select Built-in from the Schema list and then click the three-dot button next to the Edit schema field to open the dialog box where you can set the schema manually.

  8. In the Query Condition field, enter the query you want to apply. In this example, we want to retrieve the clients whose names are sForce. To do this, we use the query: "name='sForce'".

  9. For a more advanced query, select the Manual input of SOQL query and enter the query manually.

Deleting the queried contents

  1. Double-click tSalesforceOutput to display its Basic settings view and define the component properties.

  2. In the Salesforce WebService URL field, use the default URL of the Salesforce Web service or enter the URL you want to access.

  3. In the Username and Password fields, enter your login and password for the Web service.

  4. Type in your intended query timeout in the Timeout (milliseconds) field. In this example, use the default number.

  5. From the Action list, select the operation you want to carry out. In this example we select Delete to delete the sForce account selected in the previous component.

  6. From the Module list, select the object you want to access, Account in this example.

  7. Click Sync columns to retrieve the schema of the preceding component.

  8. Press Ctrl+S to save your Job.

Executing the Job

  • Press F6 to execute the Job.

    Check the content of the Account object and verify that the sForce account(s) is/are deleted from the server.

Scenario 2: Gathering erroneous data while inserting data to a module at Salesforce.com

In this scenario, data in a local file is inserted to the AdditionalNumber module. Meanwhile, erroneous data in that file is collected via a Row > Reject link.

Dragging and dropping components and linking them together

  1. Drag and drop the following components from the Palette onto the workspace: tFileInputDelimited, tSalesforceOutput and two tLogRow components.

  2. Rename tFileInputDelimited as DataToInsert, tSalesforceOutput as InsertToSalesforce, and the two tLogRow components as DataInserted as well as DataRejected respectively.

  3. Link DataToInsert to InsertToSalesforce using a Row > Main connection.

  4. Link InsertToSalesforce to DataInserted using a Row > Main connection.

  5. Link InsertToSalesforce to DataRejected using a Row > Reject connection.

    Note

    Deselect the Extended Output and Die on error check boxes in the Advanced settings view of the tSalesforceOutput component so that the Reject link is available .

Configuring the components

Configuring the data source

  1. Double-click DataToInsert to open its Basic settings view in the Component tab.

  2. In the Property Type drop-down list, select Built-In.

    Note

    You can select Repository from the Property Type drop-down list to fill in the relevant fields automatically if the relevant metadata has been stored in the Repository. For more information about Metadata, see the Talend Studio User Guide.

  3. In the File name/Stream field, type in the path of the source file, for example, E:/salesforceout.csv.

  4. In the Header field, type in 1 to retrieve the column names. Keep the default settings for other fields.

Configuring the module for data insertion

  1. Double-click InsertToSalesforce to open its Basic settings view in the Component tab.

  2. In the Username field, enter your username, for example, cantoine@talend.com.

  3. In the Password field, enter your password, for example, talendehmrEvHz2xZ8f2KlmTCymS0XU.

  4. In the Action drop-down list, select insert.

  5. In the Module drop-down list, select AdditionalNumber.

    Note

    When linking the components earlier, the Extended Output and Die on error check boxes have been deselected in the Advanced settings view so that the Reject link can appear.

  6. Keep the default settings for other fields.

Configuring the console display

  1. Double-click DataInserted to open its Basic settings view in the Component tab.

  2. In the Mode area, select Table (print values in cells of a table) for a better view.

  3. Perform the same operation for DataRejected.

  4. Press Ctrl+S to save your Job.

Executing the Job

  • Press F6 to run the Job and you can find the erroneous data (if any) is displayed in the Run view.

    As shown above, there are two Call Center ID fields that have incorrect data.

Scenario 3: Inserting AccountIDs from an Excel File to the Contact Module

In this scenario, the AccountIDs from an excel file are inserted to the Contact module at the www.salesforce.com based on the matching of LastName and Name fields.

Dragging and dropping components

  1. Drag and drop the following components from the Palette onto the workspace: tFileInputExcel, tSalesforceIntput, tMap and tSalesforceOutput.

  2. Rename tFileInputExcel as excel_source, tSalesforceIntput as load_salesforce_data, tMap as match_and_output and tSalesforceOutput as insert_to_contact_module.

  3. Link the components using a Row > Main connection.

Configuring the components

Configuring the source excel input

  1. Double-click excel_source to open its Basic settings view in the Component tab.

  2. Click the [...] button next to the File name/Stream field to select the source file.

    The content looks like:

  3. Select the All sheets check box to retrieve the data of the entire excel file.

  4. Enter 1 in the Header field as the first line lists the column names.

  5. Click the [...] button next to the Edit schema field to open the schema editor.

  6. Click the [+] button to add three columns, AccountId, LastName and Name.

  7. Click OK to close the editor. Keep other default settings as they are.

Configuring the destination module and the desired operation

  1. Double-click insert_to_contact_module to open its Basic settings view in the Component tab.

  2. In the Username and Password fields, enter your authentication credentials.

  3. Select insert in the Action list and Contact in the Module list.

  4. Click the [...] button next to Edit schema to open the schema editor.

  5. Click to copy all the columns from the output table to the input table.

  6. Click OK to close the editor.

Configuring the lookup source and establishing the mapping relations

  1. Double-click load_salesforce_data to open its Basic settings view in the Component tab.

  2. In the Username and Password fields, enter your authentication credentials.

  3. In the Module list, select the Contact module to retrieve data.

  4. Clear the Query Condition field.

  5. Double-click match_and_output to open its map editor.

  6. Select fields LastName and Name from the table row1 and drop them next to their counterparts in the table row2. This way, data from the excel file will be checked against their counterparts in the Contact module.

  7. Select fields LastName and AccountID from the table row1 and drop them next to their counterparts in the table id. This way, qualified data from the excel file will be passed to their counterpart fields in the id table.

  8. Click OK to close the map editor.

Executing the Job

  1. Press Ctrl+S to save your Job.

  2. Press F6 to run the Job.

Scenario 4: Upserting the Contact module based on mapping relationships with the external IDs in the Account module

This scenario upserts the records in the Contact module based on mapping (matching) against the external IDs in the Account module:

Linking the components

  1. Drop a tSalesforceConnection, two tFixedFlowInput, two tSalesforceInput, two tSalesforceOutput and two tLogRow from the Palette onto the workspace.

  2. Rename two tFixedFlowInput components as external ids to insert and emails to upsert, two tSalesforceInput components as Contact (in) and Account (in), two tSalesforceOutput components as Contact (out) and Account (out), and two tLogRow components as external ids inserted and emails upserted.

  3. Link tSalesforceConnection to external ids to insert using the OnSubjobOk trigger.

  4. Link external ids to insert to Account (out) using a Row > Main connection.

  5. Link external ids to insert to Account (in) using the OnSubjobOk trigger.

  6. Link Account (in) to external ids inserted using a Row > Main connection.

  7. Link Account (in) to emails to upsert using the OnSubjobOk trigger.

  8. Link emails to upsert to Contact (out) using a Row > Main connection.

  9. Link emails to upsert to Contact (in) using the OnSubjobOk trigger.

  10. Link Contact (in) to emails upserted using a Row > Main connection.

Configuring the components

  1. Double-click tSalesforceConnection to open its Basic settings view.

    In the Username and Password fields, enter the authentication credentials.

  2. Double-click external ids to insert to open its Basic settings view.

  3. Click the Edit schema button to open the schema editor.

    Click the [+] button to add three columns, namely Name, AccountID__c and AccountBizLicense__c, all of the String type. Note that AccountID__c and AccountBizLicense__c are customized fields in the Account module, with the attribute of external ID.

    Click OK to close the editor.

    Select the Use Inline Content (delimited file) check box in the Mode area and enter the data below in the Content box:

    Google;US666;C.A.666
    Talend;FR888;Paris888
  4. Double-click Account (out) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select insert in the Action list and Account in the Module list.

  5. Double-click Account (in) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select Query in the Query mode list and Account in the Module list.

    In the Query Condition box, enter the filter statement: "name like 'Tal%' OR name like 'Goo%'".

  6. Click the Edit schema button to open the schema editor.

    Click the [+] button to add three columns, namely Name, AccountID__c and AccountBizLicense__c, all of the String type.

    Click OK to close the editor.

  7. Double-click external ids inserted to open its Basic settings view.

    Select the Table (print values in cells of a table) check box for a better view of the results.

  8. Double-click emails to upsert to open its Basic settings view.

  9. Click the Edit schema button to open the schema editor.

    Click the [+] button to add four columns, namely Email, AccountID, AccountBizLicense and LastName, all of the String type.

    Click OK to close the editor.

    Select the Use Inline Content (delimited file) check box in the Mode area and enter the data below in the Content box:

    andy@talend.com;Paris888;FR888;Andy
    anderson@talend.com;C.A.666;US666;Anderson
  10. Double-click Contact (out) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select upsert in the Action list, Email in the Upsert Key Column list and Contact in the Module list.

    Go to the Advanced settings view to set the relationship mapping:

    Click the [+] button to add two lines and select AccountBizLicense and AccountID in the list under the Column name of Talend Schema column.

    Enter the lookup relationship fields in the Lookup field name column, namely Account and Account__r.

    Enter the lookup module name in the Module name column, namely Account.

    Enter the external id fields in the External id name column, namely AccountBizLicense__c and AccountID__c, which are the customized fields (with the external id attribute) in the Account module.

    Note

    • Column name of Talend Schema refers to the fields in the schema of the component preceding tSalesforceOutput. Such columns are intended to match against the external id fields specified in the External id name column, which are the fields of the lookup module specified in the Module name column.

    • Lookup field name refers to the lookup relationship fields of the module selected from the Module list in the Basic settings view. They are intended to establish relationship with the lookup module specified in the Module name column.

    • For how to define the lookup relationship fields and how to provide their correct names in the Lookup field name column, go to the Salesforce website and launch the Salesforce Data Loader application for proper actions and information.

  11. Double-click Contact (in) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select Query in the Query mode list and Contact in the Module list.

    In the Query Condition box, enter the filter statement: "Email like 'And%'".

  12. Click the Edit schema button to open the schema editor.

    Click the [+] button to add two columns, namely LastName and Email, all of the String type.

    Click OK to close the editor.

  13. Double-click emails upserted to open its Basic settings view.

    Select the Table (print values in cells of a table) check box for a better view of the results.

Executing the Job

  1. Press Ctrl + S to save the Job.

  2. Press F6 to run the Job.

    As shown above, the insert and upsert actions have been completed successfully.