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?
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
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
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.
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?
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.
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 .
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
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.
16 people are following this question.
