How to transform data type in Azure Data Factory

Question

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').
   
2. I cannot change the data type during file format settings. The data type for all column is preset to string as default.
   
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?

Answer 1

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.

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.

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.