New Salesforce connector try to convert number/currency to string

Question

Hi everyone,

We are using the new Salesforce connector in Azure data factory to get data from Account object.

SELECT Id ,POSId__c ,EcomId__c ,MasterAccount__c ,MasterPOSId__c ,Name ,Salutation ,FirstName ,LastName ,Gender__c ,DayOfBirth__pc ,MonthOfBirth__pc ,YearOfBirth__pc, Last12MSpendKnitwear__pc from Account where RecordType.Name in ('Person Account','Hidden') and LastModifiedDate = LAST_N_DAYS:7

We get the last 7 days of modification and just some type of records.

The query worked fine with the old Salesforce connector but with the new one, we get an error :

Operation on target Copy data1 failed: Une panne s'est produite côté « Source ». ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: 'salesforce-crm-account/tmp/SalesforceAccount_.csv'.,Source=Microsoft.DataTransfer.Common,''Type=System.InvalidCastException,Message=Invalid format for conversion, strValue:0.0, type: System.Decimal,Source=Microsoft.Connectors.Salesforce,'

I tried field after field and found out this conversion issue happens for number and currency salesforce format. Formula(Number) and other formats, even dates are ok.

I have the same issue even by just do a preview data in the copy data component on the Salesforce datasource.

Does the new connector do a hidden mapping? Is it possible to modify the settings?

With the old connector, the YearOf_Birth__c column returned 1798 and with the new connector, we saw it returned 1798.0 (it is in number format), as for a currency format with the Last12MSpendKnitwear__pc column, it returned 465.00 and with the new connector 465.0.

I really do not understand where the conversion is happening.

Finally, the new linked service and the new dataset are connecting fine to Salesforce.

FYI : The file in the tmp folder from the error message is not created, we do not find it anywhere, and with the logs, we have no more information to help us see where it comes from.

If someone can help us with this subject, I'd be really grateful.

Thank you.

Answer 1

Hi @Alexandra Fejjari
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Issue: We are using the new Salesforce connector in Azure data factory to get data from Account object.

SELECT Id ,POSId__c ,EcomId__c ,MasterAccount__c ,MasterPOSId__c ,Name ,Salutation ,FirstName ,LastName ,Gender__c ,DayOfBirth__pc ,MonthOfBirth__pc ,YearOfBirth__pc, Last12MSpendKnitwear__pc from Account where RecordType.Name in ('Person Account','Hidden') and LastModifiedDate = LAST_N_DAYS:7

We get the last 7 days of modification and just some type of records.

The query worked fine with the old Salesforce connector but with the new one, we get an error :

Operation on target Copy data1 failed: Une panne s'est produite côté « Source ». ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: 'salesforce-crm-account/tmp/SalesforceAccount_.csv'.,Source=Microsoft.DataTransfer.Common,''Type=System.InvalidCastException,Message=Invalid format for conversion, strValue:0.0, type: System.Decimal,Source=Microsoft.Connectors.Salesforce,'

I tried field after field and found out this conversion issue happens for number and currency salesforce format. Formula(Number) and other formats, even dates are ok.

I have the same issue even by just do a preview data in the copy data component on the Salesforce datasource.

Does the new connector do a hidden mapping? Is it possible to modify the settings?

With the old connector, the YearOf_Birth__c column returned 1798 and with the new connector, we saw it returned 1798.0 (it is in number format), as for a currency format with the Last12MSpendKnitwear__pc column, it returned 465.00 and with the new connector 465.0.

I really do not understand where the conversion is happening.

Finally, the new linked service and the new dataset are connecting fine to Salesforce.

FYI : The file in the tmp folder from the error message is not created, we do not find it anywhere, and with the logs, we have no more information to help us see where it comes from.
Solution: Error was due to IR version.

We needed to install the latest version, with 5.48.9076.1 version it is now working fine.

Here is the link : https://www.microsoft.com/en-us/download/details.aspx?id=39717

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi all,

Error was due to IR version.

We needed to install the latest version, with 5.48.9076.1 version it is now working fine.

Here is the link : https://www.microsoft.com/en-us/download/details.aspx?id=39717

Thank you for your help.

Answer 3

Hello. I have the same kind of issue importing from Salesforce Account using the SalesforceConnectorV2 version with AutoResolveIntegrationRuntime. The decimal(18,15) like BillingLatitude for example are automatically converted to decimal(38,18) in parquet file which generates failure while copying to database with decimal(18,15). I used an Azure runtime on source.

As it is a generic pipeline with automatic mapping I need to add a transformation with condition TYPE=decimal but I don't understand why conversion is incorrectly done natively.