How to include the missing columns when using an SOQL statement
The following concern applies to previous releases (before 1.9). Currently, all columns from the SOQL are automatically created, but in some cases, the data type of the column might not be retrieved from the results and a WSTR 250 would be assumed.
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.
Book a Presales Call
Book a Presales Call with us now and get 5% off SSIS+.
Related Articles
Salesforce Source: How to modify SOQL Statement property dynamically at runtime from SSIS Expression
The Salesforce SOQL Statement property is available in the data flow properties list to be set dynamically at run time. Note: The process for setting the BatchSize, Object and ObjectFilter properties for Salesforce are the same as for the Statement ...Missing fields when using SharePoint Source Component
Scenario: My custom created SharePoint fields are missing when using the SharePoint Source component Solution: A possible solution to the issue is to include the hidden fields. You should follow the steps below: Click twice on the ...Missing objects or columns when using Salesforce Destination Component
Most likely the reason for object or column to be missing is that you are using older version of Salesforce API. To verify which version of Salesforce API you are using please check what is showing in the Server URL field on the Salesforce Connection ...How to modify the source dynamically at runtime
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 ...Dynamics CRM Source: How to modify the FetchXML statement dynamically at runtime
The FetchXML statement is available in the data flow properties list. Follow these steps to set up an expression: Create a variable that can be loaded with the desired FetchXML statement. (Example: vFetchXMLModifiedOnDate) Right-click on the data ...