question

karthikshanth avatar image
0 Votes"
karthikshanth asked pavnis answered

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
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 @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 ·

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 karthikshanth commented

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?

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.

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.

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.

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)
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.

pavnis avatar image
0 Votes"
pavnis answered pavnis commented

Hello @HimanshuSinha-MSFT,

I have same issue with ADF v2. Source is ADF and Sink is Azure SQL DB.

I already tried to accept data in TABLE TYPE (FieldName01(VARCHAR(max)) and then CAST(FieldName01 as DATE) in Stored Procedure.

Thanks in advance for anyone who come across this and able to shed some light on this.

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.