Salesforce Source

Overview

The Salesforce Source Component is an SSIS Data Flow Component for consuming data from the Salesforce Service. The configuration parameters are provided in the standard data flow source component dialog.

Demonstration


Basics
11:20 min


Dynamic query
3:59 min


Lookup
9:31 min

Demonstration VideoDemonstration Video

Setup

Use the parameters below to set up the component.

Parameters

SFORCE

Select an existing Salesforce connection manager.

Related Topics: Salesforce Connection Manager

BatchSize

Specify the number of rows to be retrieved as a batch. The maximum is 2000.

InputType

Specify the input type. This parameter has the options listed in the following table.

Value Description
Object Input type is Salesforce object. Selecting this value displays the dynamic parameters Object, ObjectFilter (1.5 SR-2).
Statement Input type is a SOQL statement. Selecting this value displays the dynamic parameter Statement.

Note: After making this change, use the Refresh command in the 'Column Mappings' tab to reload the source object metadata.

Object

Select the SalesForce data object.

ObjectFilter (1.5 SR-2)

Specify the object filter statement using SOQL statement expressions.

Statement

Specify a SOQL statement.

IncludeDeleted (1.4 SR-1)

Use this parameter to include all records in the result, including the deleted records. This parameter has the options listed in the following table.

Value Description
True The deleted records are included in the result.
False The deleted records are not included in the result.

How to modify the source dynamically at runtime

The source object or statement is available in the data flow properties list. Follow these steps to set up an expression:

  • Right-click on the data flow canvas and select Properties from the menu.
  • Scroll-down and find a property named like [Salesforce Source].[Statement]. This is the property containing the statement.
  • Scroll-down and find the Expressions property. Set up an expression to modify the statement dynamically.

How to include the missing columns when using an SOQL statement

The Salesforce service only returns fields containing data. If there is no data for a specific field, no data is returned. Therefore, it is impossible to set up the appropriate field and field type. There are 3 options to include the missing columns:

Option 1

This option will work fine for SOQL statements without relationships. For statements with relationships, see the other options below.

  1. Set the InputType to Object. Select the object from which you want to retrieve data.
  2. Go to the 'Column Mappings' tab and press 'Refresh' to reload the object's columns.
  3. Set the InputType to ‘Statement’ and specify your statement. Do not refresh the columns in the 'Column Mappings' tab.
  4. If you want to remove columns you don't need, go to the 'Column Mappings' tab and remove the mappings you don't need.
Option 2
  1. Set up a temporary Salesforce record and the required relationship data.
  2. Open the 'Salesforce Source' component setup dialog and specify the SOQL statement retrieving the temporary record data.
  3. Refresh the 'Column Mappings' tab. All needed column metadata should now be included.
  4. Go back to the Salesforce setup window and remove the temporary record and all other unneeded data.
Option 3

This option requires manual definition of the missing columns.

  1. Go to the 'Input and Output Properties' tab.
  2. Go to 'Salesforce Source Output' -> 'External Columns'.
  3. Press 'Add Column'.
  4. Specify the column name and type. Make sure the name is specified properly with the appropriate structured definition.

How to download files from Salesforce

Uploaded files are located in the Attachment object. Use the standard "Export Column" transformation to export the Body field content into a file.

Related scripts