Salesforce Source
Overview
The Salesforce Source Component is an SSIS Data Flow Component for consuming data from the Salesforce Service.
Demonstration
Quick Start
In this section we will show you how to set up a Salesforce Source component.
- Before you begin, configure a Salesforce connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the Salesforce Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, select the connection manager you configured earlier from the Connection drop-down list.
- For Mode, either select Regular or Bulk.
- For Input, either select Object or Statement.
- If you selected Object, then select the object you want to work with from the drop-down list. You may also specify a Filter, as shown in the figure.
- If you selected Statement, then compose a SOQL statement to retrieve the object columns and records you want from the source.
- Click OK to close the component editor.
Congratulations! You have successfully configured the Salesforce Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Salesforce connection manager.
Related Topics: Salesforce Connection Manager
Specify the number of rows to be retrieved as a batch. The maximum is 2000.
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. After making this change, use the Refresh command in the 'Column Mappings' tab to reload the source object metadata.Select the read mode. This parameter has the options listed in the following table.
Value Description Regular Read data using regular mode (default). Bulk Read data using bulk mode. Select the Salesforce data object.
Specify the object filter statement using SOQL statement expressions.
Specify a SOQL statement.
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.
FAQ
The Object, ObjectFilter or Statement parameters are available for setup 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.
NOTE: The concern elaborated in the below paragraphs doesn't apply to the latest SSIS+ releases. Since 1.9 we have implemented an extra handling for missing data in the responses, so if at least one result row is retrieved during design-time columns analysis, then all columns from the SOQL should be automatically created. Please note that still, in some rare cases, the data type of some the columns might not be extracted from Salesforce and WSTR 250 would be assumed for such columns.
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.
Uploaded files are located in the Attachment object. Use the standard "Export Column" transformation to export the Body field content into a file.
Make sure you have at least SP2 installed. To determine current version open SSMS and enter the following query: select @@version
The reported version should be 9.00.3042.00 or above.
Related scripts
Samples
What's New
- New: Improved the metadata initialization when using SOQL statements (no more missing columns if the result contains empty fields).
- New: Support for BULK mode for SOQL statements
- Fixed: The "Preview" failed with "EXCEEDED_ID_LIMIT" error under some conditions.
- New: Component can now retrieve deleted records in bulk mode.
- New: A new parameter Mode with two options: Regular and Bulk. When bulk mode is used, the component will use Salesforce bulk read functionality.
- Fixed: Component failed with "Failed to process ..." error when processing input columns of type binary.
- New: Component now supports aggregate SOQL statements (Thank you, Greg).
- Fixed: Component failed with “INVALID_TYPE: sObject type ‘…’ is not supported.” error when processing certain SOQL statements containing relationships (Thank you, Jonlee).
- New: A new parameter ObjectFilter.
- Fixed: Component failed with "INVALID_TYPE: sObject type '...' is not supported." error when processing certain SOQL statements containing relationships (Thank you, Ben).
- Fixed: Component failed to load object with more than 250 rows of data.
- New: A new parameter IncludeDeleted to retrieve all records, including deleted.
- New: Introduced component.
Related documentation
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.