Azure Data Factory Copy activity Exception dateTime

1
votes

I am trying to move data from azure blob to azure sql Data warehouse. Azure blob has a json file.

I am getting the exception while moving createdate into the datetime type column on sql.
{ "createdDate":"2016-07-13 15:24:58.000" }

Copy activity encountered a user error at Sink:tcp:database.windows.net,1433 side: ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'createdDate' contains an invalid value '2016-07-13 15:24:58.000'. Cannot convert '2016-07-13 15:24:58.000' to type 'DateTime' with format 'yyyy-MM-dd HH:mm:ss.fffffff'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'.

Any pointers will be appreciated.

2
azureazure-data-factory
Landed here from Google, commenting for future Googlers. Data Factory kept telling me "Invalid Column Mapping" when copying DateTime columns from a CSV file into SQL Data Warehouse. In my case the issue was that Data Factory data types are case sensitive. It didn't like the fact that I had "datetime" or "Datetime" instead of "DateTime" in some of the column types. – DavidP

2 Answers

1
votes

The blob data is missing precision. It looks like the column in the destination database has a DATETIME2(7) setup, whereas the source is outputting DATETIME2(3). You need to either

  1. Fix the source data to comply
  2. Massage the data via a stored procedure for the insert
  3. if possible change the destination column to DATETIME2(3)

... theoretically of course. 😊

0
votes

You should try this which will resolve the issue. This is a trick that was a blocker for me, but try this-

  1. Go to sink
  2. Mapping
  3. Click on the output format
  4. Select the data format or time format you prefer to store the data into the sink. For data format choose 'date' for time choose 'time'. You can choose boolean true or false too. enter image description here