Salesforce.Net Transformer

Transformations using SOQL Queries

First version available: 2.1.0

Using the Salesforce.Net Transformer plugin, it is possible to use SOQL queries to retrieve information for a specific source record, leveraging the plain Salesforce SOAP API directly from the plugin.

In order to issue a SOQL query for each source record, use the following source syntax:

<SourceTransform>
  <Transform config="[path to config file]">soql.net://[SOQL query]</Transform>
  <Parameters>
    <Parameter name="[name]">[param expression]</Parameter>
    ...
  </Parameters>
</SourceTransform>

Salesforce Transformer Configuration

The configuration of the Salesforce.Net Transformer (and Salesforce.Net Writer and Salesforce.Net Writer) is stored in a separate XML configuration file.

The documentation on that can be found on the Salesforce Configuration File page. This configuration file is the same for both the Salesforce Reader and the Salesforce.Net Transformer (this plugin).

Using SOQL Queries

Standard Salesforce SOQL queries are supported. All fields which are to be retrieved from Salesforce must be separately defined in the query, as does obviously also the entity.

Example:

<Transformation>
  <Source config="delim=';'">file://account_ids.csv</Source>
  <SourceTransform>
    <Transform config="sfdc_config.xml">soql.net://select Id, Name, Revenue__c from Opportunity where AccountId='%account'</Transform>
    <Parameters>
      <Parameter name="account">$AccountId</Parameter>
    </Parameters>
  </SourceTransform>
  <Target config="delim=';'">file://opportunities_with_revenue.csv</Target>

  <Fields>
    <Field name="Id">$Id</Field>
    <Field name="Name">$Name</Field>
    <Field name="Revenue">$Revenue__c</Field>
  </Field>
</Transformation>

This example will read a list of Account IDs from account_ids.csv; it is assumed the CSV file contains a column called AccountId. For each source record, the AccountId content is passed into the SOQL query using the defined parameter called account.

Notes and limitations:

• Parameters only work in the where clause of the SOQL statement, not in the field defintions.
• If both the source and the transform contains the same fields ($(field)), the transformed field has priority over the source field
• The result of the SOQL query may be empty; in this case, the transformed fields will return an empty string
• For each transformed record in the result set, one target row is written; that means that the source and target row counts are inherently different
• In case you don't want to output anything for an empty result set, use the TransformHasResult Operator (see there).