Recently, encountered a scenario, which is a little out of the norm while using the SFDC Connector. Once the issue is understood, it is easily remedied.
The problem / Error
- SOQL run in Salesforce workbench and column returns data
- The DataStage job/ETL runs without errors or warnings
- The target column output only returns null values
In short the cause is a misalignment between the SOQL field name and the column name in the columns tab of the connector.
The fix is simply to convert the dots in the field name to underscores. Basically, a field name on SOQL of Account.RecordType.Name becomes Account_RecordType_Name.
Example Field / Column Name Fix
From Contact c
Columns Tab With Correct Naming Alignment
Please note that the qualifying dots have been converted to underscores.