How to transform data type in Azure Data Factory

benny.wong 26 Reputation points
2021-05-25T07:09:17.187+00:00

I would like to copy the data from local csv file to sql server in Azure Data Factory. The table in sql server is created already. The local csv file is exported from mysql.

When I use copy data in Azure Data Factory, there is an error "Exception occurred when converting value 'NULL' for column name 'deleted' from type 'String' to type 'DateTime'. The string was not recognized as a valid DateTime.

What I have done:

  1. I checked the original value from column name 'deleted' is NULL, without quotes(i.e. not 'NULL').
    99346-screenshot-2021-05-25-at-125905-pm.png
  2. I cannot change the data type during file format settings. The data type for all column is preset to string as default.
    99384-screenshot-2021-05-25-at-25849-pm.png 99296-screenshot-2021-05-25-at-15451-pm.png
  3. I tried to create data flow instead of copy data. I can change the data type from source projection. But the sink dataset cannot select sql server.

What can I do to copy data from CSV file to sql server via Azure Data Factory?

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

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee
    2021-05-25T09:11:08.977+00:00

    Hello @benny.wong ,

    Thanks for the question and using MS Q&A platform.

    1. If date columns in your table has similar format then you can make use of type conversion settings which is available in copy activity mappings tab.
    2. If all the date fields are having different date formats like (yyyy.MM.dd , yyyy.MM.dd HH:mm, yyyy.MM.dd HH:mm:ss) then use below workaround.
      • Using data flows first transform date columns to some fixed format and then load that transformed data to supported source like (Blob Storage/ADLS gen2).
      • Now use copy activity to load data in to SQL server.

    Using ADF - Copy Activity:

    Using Type Conversion setting you can enable the new data type conversion experience in the Copy Activity.

    dateTimeFormat: Format string when converting between dates without time zone offset and strings, for example, yyyy-MM-dd HH:mm:ss.fff.

    99426-image.png

    For more details, refer to ADF - Data type mapping.

    Using ADF - Azure Data Flow:

    Unfortunately, Azure Data Flows don't support SQL Server as a supported source types.

    Mapping data flow follows an extract, load, and transform (ELT) approach and works with staging datasets that are all in Azure. Currently, the following datasets can be used in a source transformation.

    99357-image.png

    I would suggest you to provide feedback here:

    https://feedback.azure.com/forums/270578-data-factory

    All of the feedback you share in these forums will be monitored and reviewed by the Microsoft engineering teams responsible for building Azure.

    Hope this helps. Do let us know if you any further queries.

    Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.