Azure Data Factory error while parsing String date to date SQL

Question

Azure Data Factory error while parsing String date to date SQL

Swati Tiwari 0

Hi, I am trying to copy data from a CSV to SQL server. While writing the data I am changing the schema for a column (Source Date (string) --> Target date (date)) in mapping. There is no empty value in the column. I am getting the error :

ErrorCode=SqlOperationFailed,
'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=A database operation failed. Please search error to get more details.,
Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Data.Governance.Plugins.Core.TypeConversionException,
Message=Exception occurred when converting value '24/01/2024' for column name 'EXPORT_DATE' from type 'String' (precision:, scale:) to type 'DateTime' (precision:255, scale:255). 
Additional info: String was not recognized as a valid DateTime.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2024-02-16T05:54:25.4966667+00:00

Hi Swati Tiwari, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

4 answers

Your answer

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2024-02-16T05:54:25.4966667+00:00

Hi Swati Tiwari, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 1

Greg Low 1,980 Microsoft Regional Director

The default date format for SQL Server is US date format. That date in the error (24/01/2024) is not valid for US date format. What we usually do, is to land the data directly in SQL Server as a string, and then use T-SQL code to interpret it as needed. (e.g. by using CONVERT or PARSE) If there is any chance to modify the source query, try to put the date in a region-neutral format eg: 'YYYYMMDD'. SQL Server will always interpret an 8 digit string as YYYYMMDD. (Note no dashes or slashes)

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-02-09T22:57:27.5466667+00:00

But, Greg, this is not an error that occurs in SQL Server, is it? This seems to be a parsing error in .NET.

But, yes, the root cause is the same. The format is geared for a date format that is only used in on single country, I don't know much about Azure Data Factory, but it should be possible to specify the date format somewhere.
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-10T02:51:29.4666667+00:00

Hi Erland, it's a weird mix of that and of Spark code.

There are several ways to handle this. We avoid trying to do that type of parsing in ADF. Far safer to just land the incoming date as a string, and then use CONVERT or PARSE, etc. to fix it when reading it in SQL Server. I think that's the best approach by far.

Another approach is to add a calculated column in the Source tab, and to use it to modify the date as required, then map that column in the Mapping tab rather than the column you were going to use. This is messy to work with.

Yet another approach is to use a Dataflow. It has far more control over how this works. However, we avoid Dataflows in ADF for two reasons: a) they are much slower b) they cost far more to run.
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-10T02:53:39.76+00:00

Some of the activities like Copy data allow you to modify the date format on the Mappings tab, to make sure you choose the one that's needed. Again, that's not our preference, but it might be an option for you. Which activity type are you using? Is it an actual Copy data activity?

Answer 2

Greg Low 1,980 Microsoft Regional Director

(Sorry, ended up in the wrong item)

Answer 3

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi Swati Tiwari, Thank you for posting query in Microsoft Q&A Platform.

You need to consider provided source side date format details under mappings tab. Kindly check below link to understand, how to do the same.

https://learn.microsoft.com/en-us/answers/questions/439579/does-adf-copy-acitivity-allow-to-format-date-using Hope this helps. Please let me know how it goes.

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2024-02-13T05:28:40.04+00:00

Hi Swati Tiwari, Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 4

Swati Tiwari 0

I ended up writing data flow (used derived column: concat(split(EXPORT_DATE,'/')[3],'-',split(EXPORT_DATE,'/')[2],'-',split(EXPORT_DATE,'/')[1])

Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-16T06:14:27.2766667+00:00

Data flows are much more expensive to use. Make sure you are aware of that. We generally avoid them.
Swati Tiwari 0 Reputation points

2024-02-16T06:16:52.5566667+00:00

Yes I know thanks. There are few other activities s well in same flow

Share via

Azure Data Factory error while parsing String date to date SQL

4 answers

Your answer