question

karthikshanth avatar image
0 Votes"
karthikshanth asked ·

Restrict ADF converting Null data from source date field returning 1900-01-01 in target column

Hello All, I am not sure, whether this was already asked, I did a search and didn't find any suitable replies related to this.

ADF copy data is returning '1900-01-01' by default to a NULL DateTime field when moving the data from Source to target table while doing the Polybase copy. Both of my source/target are Azure SQL DW tables

I couldn't find anything to change such settings while creating the copy command pipeline in ADF. Any pointers to restrict ADF from doing the automatic conversion?

azure-data-factoryazure-synapse-analytics
· 3
10 |1000 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 @karthikshanth ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

Hello @karthikshanth ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·
karthikshanth avatar image karthikshanth HimanshuSinha-MSFT ·

Yeah, I figured out no alternative ways. I went with an update statement post finishing the ADF polybase loads. I know, it's not the best way, but I have left with no other choice.

Plus, The simple copy wouldn't be an obvious choice, since I m dealing with huge data which takes a longer time to load the data with the option.

0 Votes 0 ·
NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered ·

Hey @karthikshanth ,
Based on my knowledge, unfortunately there is no alternative for it.
Whenever you use polybase functionality, ADF provides the default value for NULL values like 0 for int and 1900-01-01 for datetime.
There are 2 ways:
1) either keep it a simple copy activity so that null value would be retained
2) Load into a staging table and then while loading into destination convert the values back to null
@HimanshuSinha-MSFT : Any thoughts?

·
10 |1000 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.

JohnAherne-9205 avatar image
0 Votes"
JohnAherne-9205 answered ·

A not pretty solution would be to convert them to strings first, then load into a staging table. Convert them back through some sort of merge.

·
10 |1000 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.

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered ·

Thanks @NandanHegde-7720 .
Hello @karthikshanth,

Great question and I did tried to repro the issue and I was able to do that .

Took some dummy data .

 --DROP TABLE DATETEST
 CREATE TABLE DATETEST 
 (
 ID INT ,
 SOMEDATE DATETIME NULL
 )
 SELECT GETUTCDATE()
 INSERT INTO DATETEST VALUES (1,'2021-03-16 21:51:35.907')
 INSERT INTO DATETEST(ID) VALUES (2)
 --DROP TABLE DATETEST_DESTINATION
 CREATE TABLE DATETEST_DESTINATION
 (
 ID INT ,
 SOMEDATE DATETIME NULL
 ). 


Please uncheck "Use Type default" and that should do the trick . I am sharing the snapshot for reference below .

78501-image.png


78442-null-issue.png

let me know how it goes .

Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members



image.png (27.7 KiB)
null-issue.png (535.7 KiB)
·
10 |1000 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.