question

bennywong-0956 avatar image
0 Votes"
bennywong-0956 asked PRADEEPCHEEKATLA-MSFT commented

How to transform data type in Azure Data Factory

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @bennywong-0956,

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.


image.png (107.6 KiB)
image.png (60.6 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @bennywong-0956,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

I added the DateTime format, but got the same error.


100088-screenshot-2021-05-27-at-30424-pm.png


0 Votes 0 ·

Hello @bennywong-0956,

As per error message you should change date format to below:

DateTime Format: yyyy-MM-dd HH:mm:ss

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

0 Votes 0 ·
Show more comments