This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 89%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESP%3C/text%3E%3C/svg%3E)
I have a Copy data Activity which pull data from Source Parquet file to Sink Azure SQL database. while doing so its throws an error of
"Failure happened on 'Sink' side. ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'Date_column' contains an invalid value ''.,Source=Microsoft.DataTransfer.ServiceLibrary,''Type=System.ArgumentException,Message=String was not recognized as a valid DateTime.Couldn't store <> in Date_column Column. Expected type is DateTime.,Source=System.Data,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'
I have run distinct command over the Date_column column below is the output -
03/27/2022 07:42:12
03/29/2022 08:05:27
03/22/2022 07:39:05
03/23/2022 08:03:05
Distinct count is 5.
I have checked the sink table. null value is allowed for date_column
I think it causing issue while convert null value in datetime. Please help me on this!
Probably there is an empty string, which cannot be converted. Maybe it is possible to use null instead of empty strings, or to convert empty to null?
Is there any way pipeline doesn't pull null value?
Hi @Sarvesh Pandey ,
Just checking in to see if you got a chance to see the below response. If the suggested response helped you, please click Accept Answer . If you have further questions about this answer, please do Comment.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(57.599999999999994, 78%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERS%3C/text%3E%3C/svg%3E)
Hi @Sarvesh Pandey ,
Did you get a solution for this ? I have also encountered the same issue and trying to find a solution for this without using data flow.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 8%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Hello @Sarvesh Pandey
I don't think there's a NULL value in your case but a Blank value which can't be converted into string
Can you copy the value and paste it in an editor, so it will show if there are blank spaces in the string, that can be the reason why it didn't convert it into DateTime
Hi @Sarvesh Pandey ,
Just checking in to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer and kindly upvote the same. If you have further questions about this answer, please do Comment.
In my case NULL values are coming from a CSV file but it's still failing with the same error. Any ideas on how to handle this?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(38.4, 20%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJR%3C/text%3E%3C/svg%3E)
Hi all, anyone find a solution to this error yet? experiencing same in adf copy activities.
iI posted this answer above, does this help?
I had this issue while using Snowflake as a source and SQL Server as a sink.
What happened in my case (and probably in your case), is that Azure Data Factory requires the "Enable Staging" property to be turned on for the Copy Data Activity (click on copy data activity > settings).
Then, when the copy data activity is run, the data is taken from source and then loaded into this staging environment first as CSV (in my case, but yours might be similar).
Then, when it populates that CSV file, it is loading the '' [empty] value.
Then, when the sink part of the copy activity runs, it is querying the data directly from that staging environment (CSV in my case) and it is treating the '' [empty] as a string.
For a workaround, I changed the source query on the Snowflake data to use an "IFNULL" function for each date field, so that the original [NULL] value is replaced with a fake date '1901-01-01', which is then a valid date value to be loaded into SQL Server.
Your situation may be slightly different, but this would be my best guess without knowing the details of your pipeline.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(6.4, 57.99999999999999%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJD%3C/text%3E%3C/svg%3E)
I got the same error in an ADF copy data activity, copying data from many Excel files to database. Some, not all, files failed in the copy data activity. I cannot tell difference between the one failed and the others. What fixed the error is that I opened the Excel, right-click the date columns one-by-one, format cell, choosing "Date", and save file. upload the Excel files and re-ran the ADF pipeline.