data type issue in adf pipeline

Ram Babu 75

Hi Team,

when i use adf pipeline from salesforce as source and azure storage as sink, one table not loading and getting error like below

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/sfdc/DatedConversionRate.txt'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

I have try to mapping in copy activity then all data types converting as string. Pls see below image.

User's image

How to handle this issue. all other tables are loading but this one facing problem. Pls have a look and advice.

I tried using data flow and data flow not supported to salesforce as source.

Thanks

phemanth 10,010 Reputation points Microsoft Vendor

2024-08-20T13:20:41.5266667+00:00
@Ram Babu

Welcome to the Microsoft Q&A and thank you for posting your questions here

It seems like you're having trouble loading a table with data from Salesforce into Azure storage due to a DateTime format issue. Here's a breakdown of the problem and how to fix it:

The error message "String was not recognized as a valid DateTime" indicates that your Salesforce data, specifically in the DatedConversionRate.txt file, contains date values that are not formatted in a way that Azure can easily recognize. This is happening because you've mapped all data types to string in your copy activity.

Solution

To resolve this, you'll need to handle the date formatting directly within your copy activity. You can achieve this by using a combination of mapping and data transformation techniques:

Mapping: Ensure that your mappings correctly identify the columns in the DatedConversionRate.txt file that represent dates.

Date Conversion: You can use the toDate() function in your copy activity to convert the string representation of the date into a valid Azure DateTime format.

Check Source Data: Examine the DatedConversionRate.txt file directly to see how dates are formatted. This will help you choose the correct toDate() format.

Format Consistency: Make sure your date formats in the source data are consistently applied to all records.

Error Handling: Use error handling mechanisms in your copy activity to gracefully handle any remaining date format discrepancies.

Hope this helps. Do let us know if you any further queries.
Ram Babu 75 Reputation points

2024-08-20T13:59:05.6133333+00:00

where to apply toDate() function in copy activity. In Mapping section there are no modification option of data type. There is no source issue. Earlier table was loaded but from last two weeks not loading. There are no changes in source also from last 2 months. How the error happened all of sudden. There is no options to choose format in copy activity. Pls advise on this.
phemanth 10,010 Reputation points Microsoft Vendor

2024-08-23T11:44:44.2433333+00:00

@Ram Babu could you please provide more details of your copy activity configuration and settings with screen shots
phemanth 10,010 Reputation points Microsoft Vendor

2024-08-26T11:40:46.5833333+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 75 Reputation points

2024-08-27T08:22:21.3533333+00:00

Hi, issue still going on.
I am loading 24 tables from salesforce to azure storage. one of the table crating issue with data type as mentioned above. earlier 2 weeks back, no issue with load but recently getting issue.

How it was working for very long time. The data was same for long time. This is the question for every one. Even synpase pipeline able to load data without issue and why can't blob storage. SFDC object have date like 0001-01-01 when even they added new currency code. This table is system generated. how to resolve issue with this type. pls advice. Thanks
phemanth 10,010 Reputation points Microsoft Vendor

2024-08-28T11:33:48.0333333+00:00
@Ram Babu

It looks like the issue is related to the specific date value 0001-01-01 in your Salesforce data. This date is likely causing the System.FormatException because it is not recognized as a valid DateTime format in Azure Storage.

Here are some steps to address this:

Data Transformation: Use a Data Flow activity in Azure Data Factory to transform the date format before loading it into Azure Storage. You can replace invalid dates like 0001-01-01 with a default valid date or handle them as null values.

Custom Mapping: In your Copy Activity, create a custom mapping for the date columns. Use the toDate() function to convert the string representation of the date into a valid DateTime format. For example:
{ "type": "Expression", "value": "toDate(sourceColumn, 'yyyy-MM-dd')" }

Error Handling: Implement error handling in your pipeline to catch and log any rows with invalid date formats. This will help you identify and address any problematic data.

Data Consistency: Ensure that the date formats in your Salesforce data are consistent. If possible, update the source data to use a valid date format.
Ram Babu 75 Reputation points

2024-08-30T05:14:46.7566667+00:00

Data flow won't support salesforce and custom activity not working.
I have changed Integration run time from azure auto resolve IR to SHIR in blob storage then table was able to load.
why azure auto IR not worked at the same time SHIR worked. Is there any configuration required in azure auto IR.
using SHIR for azure blob storage, the date was loaded as 0001-01-03 instead of 0001-01-01.
pls advice on this. Thanks
phemanth 10,010 Reputation points Microsoft Vendor

2024-09-02T17:36:16.4766667+00:00
@Ram Babu

Regarding the difference in behavior between Azure AutoResolve IR and SHIR, it's likely due to the way they handle date formats. Azure AutoResolve IR might be using a different date format or parsing mechanism that's causing the issue, whereas SHIR is using a different approach that's allowing the data to be loaded.

As for the date being loaded as 0001-01-03 instead of 0001-01-01, it's possible that there's a configuration or setting in SHIR that's causing the date to be shifted by 2 days. I would recommend checking the SHIR configuration and settings to see if there's anything related to date formats or parsing that could be causing this issue.

To troubleshoot this further, I would suggest the following:

Check the SHIR configuration and settings to see if there's anything related to date formats or parsing that could be causing the issue.

Verify that the date format in the Salesforce data is consistent and valid.

Try using a different date format or parsing mechanism in SHIR to see if it makes a difference.

If possible, try using a different IR or deployment option to see if the issue is specific to SHIR or Azure AutoResolve IR.

If you're still having trouble, please provide more details about your SHIR configuration and settings, and I'll do my best to help you troubleshoot the issue.
Ram Babu 75 Reputation points

2024-09-04T12:56:09.55+00:00

Hi, Can i create new custom Azure auto resolve IR or existing one with allow all formats if any date or string can be loaded into blob storage like SHIR. whether it is 0001-01-01 or 0000-12-30.
I have no access to see SHIR configuration. Please advise.
phemanth 10,010 Reputation points Microsoft Vendor

2024-09-04T15:38:03.17+00:00
@Ram Babu

Yes, you can create a new custom Azure Integration Runtime (IR) that can handle various date formats. Here are the steps to create a new Azure IR:

Navigate to Azure Data Factory: Go to your Azure Data Factory instance in the Azure portal.

Create a New Integration Runtime:

In the Manage section, select Integration Runtimes.

Click on New to create a new IR.

Choose Azure as the type of IR.

Configure the IR:

Provide a name for your IR.

Select the region closest to your data source or sink.

Enable the Auto Resolve option to allow the IR to automatically detect the most suitable location based on your data factory’s region.

Set Up Data Flow:

If you need to handle specific date formats, consider using a Data Flow activity to transform the data before loading it into Azure Blob Storage. This can help you convert date strings to a valid DateTime format.

Error Handling:

Implement error handling mechanisms to manage any discrepancies in date formats.

Unfortunately, you cannot directly modify the SHIR configuration if you don’t have access.

creating a new Azure IR with the above settings should help you handle various date formats more effectively

1 answer

Amira Bedhiafi 22,616 Reputation points

2024-08-19T11:23:54.1733333+00:00
Try to explicitly set the type for each column like this :

https://stackoverflow.com/questions/56948054/why-does-azure-data-factory-seemingly-insist-on-inserting-datetimes-as-string

{"type": "Copy", "inputs": [ { "name": "sourceDataset" } ], "outputs": [ { "name": "sinkDataset" } ], "typeProperties": { "source": { "type": "SalesforceSource", "query": "SELECT Id, Name, CreatedDate FROM TableName" }, "sink": { "type": "AzureBlobStorage", "format": { "type": "TextFormat", "columnDelimiter": "," }, "writeBatchSize": 10000 }, "translator": { "type": "TabularTranslator", "mappings": [ { "source": { "name": "CreatedDate", "type": "DateTime" }, "sink": { "name": "CreatedDate", "type": "DateTime" } } ] } } ```}
Please sign in to rate this answer.
Ram Babu 75 Reputation points

2024-08-19T11:33:21.8933333+00:00

i didn't get anything from your response. this is single pipeline with 24 tables. all are good except this one table facing datatype issue. why data is not loading into blob storage for this particular table while rest of all tables have same type of fields and data type which are not causing any issue.
Mapping tab -> Import schemas giving all the data type as string instead of specifit.

Please advice.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

data type issue in adf pipeline

1 answer

Your answer