unable to load data from salesforce to azure storage via ADF

Ram Babu 45

Hi team,

i built a adf pipeline using look up activity & copay activity, i am loading data from salesforce objects to azure storage adlsgen2 container.

I have total 20 tables and 19 tables are successfully loaded into storage account and one table have issue. please have a look into below error

Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The file operation is failed, upload file failed at path: 'raw/sales/DatedRate'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

but the same table able to loaded into synapse not into azure storage. Please have a look and advice.

Thanks

Harishga 3,260 Reputation points Microsoft Vendor

2024-03-29T10:14:37.2+00:00
Hi @Ram Babu

Welcome to Microsoft Q&A platform and thanks for posting your question here.

Issue Analysis

Based on the error message you provided, it seems that the issue is related to the format of the date/time field in the data being loaded from Salesforce. The error message states that the string was not recognized as a valid DateTime, which suggests that the date/time format in the data is not in the expected format.

Possible Reasons

Regarding why the same table is able to load into Synapse Analytics but not into Azure Storage ADLS Gen2, it is possible that the storage format for dates is different in Synapse Analytics. You can check the storage format for dates in Synapse Analytics and ensure that it matches the expected format.

To resolve this issue, you can try the following steps:

Check the date/time format in the data being loaded from Salesforce. Ensure that the date/time format is in the expected format.

If the date/time format is not in the expected format, you can use the Data Flow activity in Azure Data Factory to transform the date/time format to the expected format before loading it into Azure Storage ADLS Gen2.

If the problem continues, you can consider loading the data into a different file format, such as CSV or Parquet, and verifying the storage format for dates in ADLS to ensure that it matches the expected format.

Check the file path, you can review the error message and ensure that the path specified in the error message matches the actual path where the file is located. You can also check the permissions for the file path to ensure that the necessary permissions are in place to access the file.

I hope this helps you resolve the issue.
Harishga 3,260 Reputation points Microsoft Vendor

2024-04-01T04:35:25.7366667+00:00

@Ram Babu
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ram Babu 45 Reputation points

2024-04-03T07:07:20.8533333+00:00

we didn't do anything for this issue. after 2 days, table was loaded successfully without any error.

my concern is why the error came suddenly without any changes in source side. Its already impacted production load in 2 days.

we are loading data into ADLS gen2. regardless of any changes in source side, the data should be loaded into container but sometimes got errors like this.
Harishga 3,260 Reputation points Microsoft Vendor

2024-04-03T10:04:51.6366667+00:00

Hi @Ram Babu
Regarding your concern about the sudden error without any changes in the source side, it's possible that the issue was caused by a temporary network or connectivity issue between Salesforce and Azure Storage ADLS Gen2.

To avoid such issues in the future, you can implement retry policies in your pipeline to automatically retry failed activities. You can also monitor the pipeline and data transfer process to quickly identify and resolve any issues that may arise.

You can review the Azure Storage ADLS Gen2 diagnostic logs to identify any issues that may have occurred during the data transfer process. The diagnostic logs can provide detailed information about the data transfer process, including any errors or warnings that may have occurred.

I hope this information helps you.