Skip to main content

Pentaho+ documentation is moving!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Salesforce Input

 

Parent article

The Salesforce Input step reads data directly from Salesforce using the Salesforce Web Service.

You can also use the following PDI steps for various ways to modify your Salesforce database:

General

 

Enter the following information in the transformation step name field:

  • Step name: Specifies the unique name of the Salesforce Input transformation step on the canvas. You can customize the name or leave it as the default.

Options

 

The Salesforce Input step features several tabs with fields. Each tab is described below.

Salesforce Input

Settings tab

 

On the Settings tab, you can configure connection and settings properties. You can test the connection and settings before proceeding.

Connection

 

In this panel, you can set the Salesforce webservice URL and credentials.

Option Description
Salesforce Webservice URL

Specify the URL to the Salesforce Webservice.

The URL specified is dependent on the API version used by PDI:

  • PDI 7.0, 7.1, and 8.0 versions use API version 37.0.
  • PDI 8.1, 8.2, and 8.3 versions use API version 41.0.
  • PDI 9.0 and later versions use API version 47.0.
Username Specify the user name for authenticating to Salesforce (myname@hitachivantara.com for example).
Password Specify the password for authenticating to Salesforce. Enter your password followed by your security token. For example, if your password is 'password' and your security token is 'token', enter passwordtoken in this field.
Test connection Click to verify the connection can be made to the specified Salesforce Webservice URL.

Settings

 

In this panel, you can set the module to query from as well as the query conditions.

Option Description
Specify query Select this check box to manually execute a query based on your own SOQL statements.
Module

Select the module (table) from which you want to retrieve data.

This list will be populated upon successfully authenticating to Salesforce using the Test connection button.

Query Condition

Enter any query filters you want to apply. Variables are allowed. For example, fieldname=myvalue AND fieldname2=myvalue2 .

Note that you do not need to include WHERE in your condition statement.

Content tab

 

The Content tab allows you to optionally include additional descriptive fields in the result set.

Content tab in Salesforce             Input

Advanced

 

Use these options to further refine the data returned from the queries specified in the Settings tab. For example, you may want to only query deleted records within a specified date range. The Advanced panel includes the following fields.

Option Description
Retrieve Select which records you want to retrieve to further define your pool of data. You can select All, Updated, or Deleted.
Query all records Select the check box to query all the records you are retrieving. Clear the check box to use the date fields (below) to define a range of records to query.
Start date Specify the starting date for retrieving the records in the date range. The format is yyyy-MM-dd HH:mm:ss.
End date Specify the end date for retrieving the records in the date range. The format is yyyy-MM-dd HH:mm:ss.

Additional fields

 

This panel includes the following fields.

Option Description
Include URL in output? & URL fieldname Select this check box, then enter the URL used to retrieve the data.
Include Module in output? & Module fieldname Select this check box, then enter the name of the module from which the data was retrieved.
Include SQL in output? & SQL fieldname Select this check box, then enter the SQL used to generate the result set.
Include timestamp in output? & Timestamp fieldname Select this check box, then enter the timestamp for when the record was retrieved.
Include Rownum in output? & Rownum fieldname Select this check box, then enter the row number for each record retrieved.
Include deletion date in output? & Deletion date fieldname

Select this check box, then enter the deletion date for when the record was removed.

Note that this set of fields is only enabled when the Advanced > Retrieve option is set to Deleted.

Other Fields

 

Enter information for the remaining fields on the Content tab.

Option Description
Time out Specify the timeout interval in milliseconds before the step times out.
Use compression Select to compress (.gzip) the data when connecting between PDI and Salesforce.
Limit

Specify the maximum number of records to retrieve.

When you set the limit to 0, there is no limit on the number of records that can be retrieved.

Fields tab

 

The Fields tab displays the fields that are read from the Salesforce module selected on the Settings tab. You will need to go to the Fields tab and press the Get Fields button to populate the fields returned before you can preview the rows returned.

Fields tab in Salesforce               Input

The following are the properties display in the Fields tab.

Option Description
Name The name of the input field.
Field The name of the field that contains the record.
IsIdLookup? Specify if the field will be used as a fieldname ID (links to an External ID) when accessing the database to perform other calls, such as delete, insert, update, and upsert. Select ‘Y’(Yes) or ‘N’(No).
Type The data type of the field.
Format An optional mask for converting the format of the original field.
Length The length of the field depends on the following field types:
  • Number

    Total number of significant figures in a number

  • String

    Total length of string

  • Date

    Length of printed output of the string (for example, four is a length for a year)

Precision Number of floating point digits for number-type fields.
Currency Symbol used to represent currencies.

For example: $ or

Decimal A decimal point can be a . or , (5,000.00 or 5.000,00 for example).
Group A grouping can be a , or . (5,000.00 or 5.000,00 for example).
Trim type The trimming method to apply to a string. Trimming only works when no field length is specified.
Repeat If the corresponding value in this row is empty, repeat the one from the last time it was not empty. Select Y(Yes) or N(No).

See Understanding PDI data types and field metadata to maximize the efficiency of your transformation and job results.

Metadata injection support

 

All fields of this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.