Synapse dataflow connecting to D365FO CDM in ADLSG2 as source, reads Date & DateTime from CSV files values as Null

Manish Moothedath 5

I have a Synapse dataflow that is connecting to a CDM folder from D365FO service in ADLSG2. The data files are in CSV and are read correctly as intended. Only the Date or Date Time fields are read as "NULL" across all Source datasets created in the Dataflow.
I already tried modifying the schema to string and with derived column expressions, but no luck.

My source output and resolve cdm.Json for the table is below: appreciate any help in resolving this.

User's image

Vedant Desai 656 Reputation points

2023-05-16T13:20:06.2833333+00:00
Hey Manish

Please share the Pipeline JSON for reference if possible.

Otherwise,Here are some things you can try to resolve the issue:

Make sure that the date and time formats in the CSV files match the date and time formats that are expected by the CDM folder. You can find the expected date and time formats in the CDM folder's schema.

If the date and time formats do not match, you can use a derived column expression to convert the dates and times to the expected format.

If you are still having trouble, you can contact Microsoft support for help.

Here are the steps on how to use a derived column expression to convert dates and times to the expected format:

In the Dataflow, click on the "Derived Column" activity.

In the "Expression" field, enter the following expression:

toDate(columnName, "yyyy-MM-dd")

where "columnName" is the name of the date or time column.

Click on the "OK" button.

The date or time column will now be converted to the expected format.
Manish Moothedath 5 Reputation points

2023-05-17T01:24:44.98+00:00

Hi Vedant,
PDA the Dataflow Json (renamed with .txt due to restrictions in attachments)
dataflow1.json.txt

The issue is, From the source itself the date values are not coming up in the output, I tried adding a derived column with expressions and it is not working. Are you suggesting reading from source with an expression rather than as string or timestamp type?

1 answer

Manish Moothedath 5 Reputation points

2023-06-09T03:15:28.25+00:00

Finally found the solution:
Enabled "Infer drifted column types"
and select all possible formats for time I have in the csv files.
Preview Data: Under Refresh >> Re fetch the data from source, and DateTime values are now showing up!!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

Synapse dataflow connecting to D365FO CDM in ADLSG2 as source, reads Date & DateTime from CSV files values as Null

1 answer

Your answer