Column is always getting null values in adf data flows when using Common Data Model Inline Dataset as source

Amar Agnihotri 921

Hi ,
I have created a dataflow in adf which is using CDM Inline dataset and model.json file for column mapping and just creating a csv file . This is my dataflow

It is using model.json file for the column mapping

This is my source file which will be copied as a .csv file . Her you can see that it is having only data but no column names. You can see that it is having created dates

and this is the model.json file which is having all the columns of the above data

Now my dataflow is grabbing all the data from the source file and mapping all the columns using model.json file

These are the settings of the dataflow

But in the data preview see the values inside created are coming NULL

This is my sink settings in dataflow

Why it is not pulling the data for created column and giving null values there. ?Where am i doing wrong ? Please suggest something to get it done .

Thanks

MartinJaffer-MSFT 26,061 Reputation points

2022-07-26T18:27:51.17+00:00
Welcome back @Amar Agnihotri

I see you need help troubleshooting why some (date / time) columns are coming up with only nulls.

My first thought is something wrong in the date/time parsing. However, best to be methodical.

First , let us try a type change.

Go to the script of the Dataflow. Save a copy of the script in notepad or somewhere temporary.

In the script, change the data type of the columns missing data to string

Refresh the data preview to see if the columns are still null.

undo the changes to the script. (this is why you made a copy)

If the data showed up when type was set to string, and does not show up when set as date/time , then this means there is a problem with the format of the date / time.
If the data is still missing even as string, then there is a deeper problem.

formats can be set in the

I notice the column name is Resolved (MM/dd/yyyy) and the data looks like 7/25/2022 12:59:25 PM and 7/25/2022.
Technically, pedanticaly, for this format of data, MM/dd/yyyy is not correct. It should be M/dd/yyyy. MM expects to see 07 not 7. a single M omits the leading 0. Double MM expects the leading 0.
I think the format string should be M/dd/yyyy hh:mm:ss a or something close to that.
Given the built in options all show 2 MM not single M , the double should be okay.

Let me know if specifying the format helps.
MartinJaffer-MSFT 26,061 Reputation points

2022-08-18T16:18:26.997+00:00

Hello @Amar Agnihotri ,
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 have already reached out to the Microsoft Support , please do share the SR# , otherwise request you to please do the needful as requested in the last response .

Thanks
Martin

1 answer

Amar Agnihotri 921 Reputation points

2022-07-27T11:45:37.1+00:00

@MartinJaffer-MSFT

Thanks for the reply . This is my dataflowscript

and i changed datatype to string

but still getting null values in date column

I also checked this import schema option but no ways..
Please sign in to rate this answer.
MartinJaffer-MSFT 26,061 Reputation points

2022-07-27T17:49:46.537+00:00

String ought to allow anything through. Since you showed me it did help at all, there might be something wrong with the CDM part of dataflow itself.

There is one last thing to try. Make a dataflow source as CSV pointing at that data file. Since your cdm data file is csv anyway, a csv dataset/inline ought to work.
If CSV source type works where CDM source type fails, then there is a product defect. If CSV also fails, then there must be something wrong in the data file. As you shared screenshot of data file, is unlikely CSV source will fail.

So, I will probably end up sending you to Support services. Support services will be able to look into things I can't, and have a more direct line to product team. This is important because I can't seem to set up things to repro the problem. CDM has always given me trouble.

With this in mind, do you have a support plan @Amar Agnihotri , or would you like a 1-time free support ticket?

Amar Agnihotri 921 Reputation points

2022-07-28T05:11:21.473+00:00

Hi @MartinJaffer-MSFT ,

Actually. My dataflow is using inline source from datalake . Model.json file is containing metadata and other container is containing files with data

This is my model.json file and you can see here that are columns which are having date and dateTime data type

That's shy we needed to change these data type in model.json, not only in dataflow script and i did the same . When i changed these datatype to string in both model.json file and dataflow script i ham getting the data. But the question is why dataflow was not supporting date and dateTime data type . Is this a limitation of dataflow or some any other restriction?

Now i have another question . Actually my files having data are being created each day and the model.json file is getting updated at the same time. These are the snapshots file which are containing data
The highlighted one is the latest one

and you can see that model.json file got updated at the same day and time

I am using this model.json file for the schema and the above snapshots file for the data via the CDM foolder approach in dataflow

Amar Agnihotri 921 Reputation points

2022-07-28T05:12:41.32+00:00

continued .....

@MartinJaffer-MSFT

But since we are getting new snapshot file each day so i want dataflow to pick up only latest snapshot file based on the last modified date. For example in the current case the highlighted one

Can you please suggest how can we achieve this in dataflow?

MartinJaffer-MSFT 26,061 Reputation points

2022-08-09T20:26:15.06+00:00

But the question is why dataflow was not supporting date and dateTime data type . Is this a limitation of dataflow or some any other restriction?

I can't imagine why it would not support date. So there must be a bug somewhere. Also, it was really interesting that you had to change data type both in script AND in model.json. I expected the script would override the model.json.

Given all the odd behavior, I'd like to offer you a support ticket so engineers can get to the root of the issue. Details coming in separate private message @Amar Agnihotri .

MartinJaffer-MSFT 26,061 Reputation points

2022-08-09T20:30:32.687+00:00

But since we are getting new snapshot file each day so i want dataflow to pick up only latest snapshot file based on the last modified date. For example in the current case the highlighted one

@Amar Agnihotri this ask if different from the original question. I would like to answer that in a separate Q&A thread to make our content more searchable.

Haider Ali 1 Reputation point

2023-02-24T06:09:05.4766667+00:00

Hi, I am also facing this same problem. In one of the data flow, datetime is coming fine but in another dataflow it is giving null values. Did anyone got resolution to this?

Thanks

Haider

Amar Agnihotri 921 Reputation points

2023-02-24T06:21:09.38+00:00

@Haider Ali

what is the data source ? In my case dataflow was getting data from the CDM folder .

Haider Ali 1 Reputation point

2023-04-05T15:32:51.28+00:00

@Amar Agnihotri We were also using CDM.
Sign in to comment

Share via

Column is always getting null values in adf data flows when using Common Data Model Inline Dataset as source

1 answer