DateTime source values showing as NULL in Azure Data Factory - Data Flows

Vivek Komarla Bhaskar 911

Hi,

Is there a reason why my data flow cannot read/treat the DateTime input column?

In my source file, I have a column named 'Date' with DateTime values of format 'yyyy-MM-dd HH:mm:ss', but the data preview always shows NULL values. Hence, to fix this issue, I updated the type from the source projection (From Timestamp to varchar), and then added a derived activity to convert to timestamp, but even here I receive NULL results. I'm not sure what's wrong.

Screenshot 2023-03-22 at 8.37.55 pm

AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2023-03-24T13:18:54.2133333+00:00

Vivek Komarla Bhaskar ,

Thankyou for using Microsoft Q&A platform and thanks for posting your queries.

I understand that your timestamp data in 'Date' column is being treated as a String column and the records are coming as NULL when you preview the data in source transformation of mapping dataflow.

I tried to repro your scenario and found out that if the column name in the source file is having any leading or trailing spaces , it might cause the data to be interpreted as NULL. Kindly check if the date column is having any spaces before or after the actual columnname.

Please let me know how it goes. Thankyou
Vivek Komarla Bhaskar 911 Reputation points

2023-03-24T19:48:42.3033333+00:00

Hi @AnnuKumari-MSFT My input columns are also enclosed in double-quoted characters and don't see any space in my input. Not sure why this is occurring at my side
AnnuKumari-MSFT 30,601 Reputation points Microsoft Employee

2023-03-27T05:36:19.6+00:00

@Vivek Komarla Bhaskar ,

By default, double quotes are treated as the quote character . Have you changed it?
Michael Strohmayer 96 Reputation points

2023-09-25T06:56:13.9866667+00:00

Did you try modifing the Schema as described here? https://strohmayer.dev/2023/09/20/azure-data-factory-fix-null-values-in-date-colums/
Anuganti Suresh 200 Reputation points

2023-12-08T05:09:06.2666667+00:00

@Michael Strohmayer , thanks for your post. It is very useful.
Anuganti Suresh 200 Reputation points

2023-12-08T05:15:09.1733333+00:00

@AnnuKumari-MSFT , Your solution works for .csv file with copy activity. But solution required for nested JSON file and using data flows.

1 answer

Suba Balaji 11,186 Reputation points

2023-03-23T14:46:35.72+00:00

Hi @Vivek Komarla Bhaskar

Can you please try this?

First you need to tell the derived column about your current source date format, and then you can use toString with whatever format you want.

toString(toTimestamp(Date,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd HH:mm:SS')

Let us know how it goes.
Please sign in to rate this answer.
Vivek Komarla Bhaskar 911 Reputation points

2023-03-23T23:32:46.2766667+00:00

Hi @Suba Balaji Still the same. Also at the sink, my table DATE column is set up as SHORTDATETIME, and you're asking me to convert it to String, which will again result in NULL data when inserting values there.

Suba Balaji 11,186 Reputation points

2023-03-24T00:44:45.07+00:00

Hi, pl don't convert the source type. From timestamp to varchar. And then try the above expression and see if that works. Only if we see the values properly in the source data preview it will flow till the end properly. So pls make sure

Vivek Komarla Bhaskar 911 Reputation points

2023-03-24T10:00:07.92+00:00

Hi, @Subashri Vasudevan I'm not sure if you understood my question correctly. Please see below detailed explanation -

My Date column at my source is of Type Timestamp and the same is reflected in my source projection.

But when I preview the data it is null. So only if change the Type from Timestamp to Varchar I'm able to see the values.

So when the source data preview is showing NULL values, no matter what we do at later stages it will always be NULL and hence none of your above-provided expressions works.

My ask here is my sink type for the Date column is set to SMALLDATETIME. Hence I need to ensure I pass Timestamp values instead Varchar (If I change the Type at my source & sink to Varchar then there are no issues with data reporting as NULL, the issue is I can't set the sink type to varchar)
Sign in to comment