Azure Data Factory: CDM Inline Dataset - Error on parsing datetime field

Guru Prasath Poongodi Kandasamy 16 Reputation points
2020-08-17T12:14:20.357+00:00

Hi,

I am trying to use the CDM inline dataset feature in the data flow. I could see the data parsed successfully except for the "datetime" fields. All datetime fields are parsed as NULLS though my source CDM data has values in it.

I was suggested to set the datatime format while importing the schema. However, none of the format seems to be parsing the field. I have also noticed that whichever format I select when I click "import Schema" again it defaults to this format "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'".

I am attaching sample screenshot from my CDM data csv file here for reference.

17936-image.png
17937-image.png

@MarkKromer-8019

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,599 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ellens, Tim 16 Reputation points
    2020-08-24T13:19:57.66+00:00

    This worked for me as well.
    However I have another issue. Datetimeoffset fields are not working.

    I have the field createdon (directly from Export to Datalake from the CDM) formatted like: 2020-01-03T07:46:37.0000000+00:00

    I tried the formats in the list or adding new ones by code, but they don't seem to work. Do you have the same issue and know how to resolve this? @MarkKromer-8019

    2 people found this answer helpful.
  2. MarkKromer-MSFT 5,186 Reputation points Microsoft Employee
    2020-08-17T16:45:29.74+00:00

    It looks like this needs be set in the Schema Drift Options as opposed to the Import Schema option for CDM inline sources. Click on Schema Drift Options, Infer Data Types, and set the timestamp proper format there.

    17939-image.png

    1 person found this answer helpful.
    0 comments
  3. Guru Prasath Poongodi Kandasamy 16 Reputation points
    2020-08-19T14:52:53.517+00:00

    Many thanks @MarkKromer-8019 . The issue is resolved after following the changes you have suggested.

    0 comments
  4. Guru Prasath Poongodi Kandasamy 16 Reputation points
    2020-09-07T09:22:26.053+00:00

    @Ellens, Tim Please use this format ('yyyy-MM-dd\'T\'HH:mm:ss.SSS') , add this in your JSON file directly to get this data ( 2020-01-03T07:46:37.0000000+00:00) parsed.

  5. Ellens, Tim 16 Reputation points
    2020-09-09T15:26:51.143+00:00

    @Guru Prasath Poongodi Kandasamy Legend! This worked! Thanks a lot!