Error while mapping .tsv file data with Azure data warehouse using data factory

Question

I wanted to copy data from form data lake present in .tsv file to Azure data warehouse but there are 2 columns that are in date datatype and because of that I am facing issues while mapping the columns. And because of that I am not able to load the data. I can use u-SQL activity only if needed.

Can you show us some of the data in your tsv file? Azure SQL data warehouse support date datatype, please check if the date string in your tsv file is correctly? Please reference Supported string literal formats for date — Leon Yue
In .tsv the column is in string data is like "20100101" and I want to map this column with data warehouse table having a datatype date — gaurav modi
Do your error message same with me? 'Message=Column '\t'dd'' contains an invalid value '\t'20100101''. Cannot convert '\t'20100101'' to type 'DateTime'.' You can edit your problem and give more details with screenshots. — Leon Yue
yes I am getting the same error but I have tried to convert the column into date-time using u-SQL activity after that I am getting the date like "1900-01-01T00:00:00.0000000" so actually it was like 19000101 but now even after that I am not able to do map the column and load this. Also, I want the date to be like "1900-01-01" but I am getting this in datetime2 format. — gaurav modi
Please see my answer, you can follow my step to solve the error. — Leon Yue

Leon Yue Leon Yue · Accepted Answer · 2019-10-21T09:55:56

I tried and tested, always get the error message:

"ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=Column 'dd' contains an invalid value '20190101'.,Source=Microsoft.DataTransfer.ServiceLibrary,''Type=System.ArgumentException,Message=String was not recognized as a valid DateTime.Couldn't store <20190101> in dd Column.  Expected type is DateTime.,Source=System.Data,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'",
        "failureType": "UserError",
        "target": "Copy_b70"

If you don't want to change your tsv file, I suggest you to using the Data Flow Derived Column. It can help covert the "20100101" data to a correct date format.

Derived Column:

Using this Expression for field Column "20100101":

   add( (substring(Column_2, 1, 4)),'-')+
   add((substring(Column_2, 5, 2)), '-')+
   substring(Column_2, 7, 2)

It works ok.

Hope this helps.

Error while mapping .tsv file data with Azure data warehouse using data factory

1 Answers