ADF Copy Activity - SQL Server Source to Avro Sink - Datatype Issues

Question

I have a number of ADF pipelines to copy data from different sources, such as Netezza and SQL Server and save as an avro file in ADLS.

When the copy activity is run from Netezza to the avro sink, column datatypes are preserved - i.e. dates and datetime's are described as date/datetime at the top of the avro file.

When the copy activity is run from SQL Server to the same avro sink, column datatypes are ignored - e.g. dates and datetime's are described as strings at the top of the avro file.

Given that the sink has no differences, I can only think its driven by the source, why would SQL server behave in this way and is there any way to fix this?

Answer 1

Hello @Shaun ,
Thanks for the ask and using Microsoft Q&A platform .
I did checked with SQL Server and you are correct that the datetime/time is mapped to string on the AVRO file , but I think in the document here , talks abou this .
https://learn.microsoft.com/en-us/azure/data-factory/format-avro#data-type-support

Honestly I am not sure as how it is working with Netezza .

I suggest you to try out the Azure data flow and see if that helps . I did tested that its capture the complex data type .

SQL Scripts

CREATE TABLE DATE_DATETIME
(
foo_date date ,
foo_datetime datetime
)
INSERT INTO DATE_DATETIME
select convert(date,getutcdate()),getutcdate()

select * from DATE_DATETIME

In ADF the AVRO has the structure as

,{"name":"foo_datetime","type":["string","null"]

in Mapping data flow it is .

{"name":"foo_datetime","type":[{"type":"long","logicalType":"timestamp-micros"

Please do let me know how it goes .
Thanks
Himanshu