Fixing Timespan DataType error in ADF

sam nick 186 Reputation points
2021-03-18T19:51:22.183+00:00

Hi ,
I am loading data from Salesforce to a Azure SQL via a simple Copy data activity. I have a precopy script to drop the destination table and reload the table from Salesforce. But i keep getting the below error.

ErrorCode=TypeConversionNotSupportedDataTypes,Exception occurred when converting value '' for column name 'Rep_Call_time__C' from type 'TimeSpan' (precision:12, scale:3) to type 'DateTime' (precision:23, scale:3)

Since i am dropping and recreeating the table, how can I update the datatype of the destination table ? Also to what should it be modified to?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,333 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 33,146 Reputation points Microsoft Employee
    2021-03-18T21:04:46.747+00:00

    Hi @sam nick ,

    Thanks for reaching out.

    As per the error message, it looks like you are trying to convert a TimeSpan data type to DateTime datatype. And as per the ADF documentation below are supported Data type mapping for Salesforce and I don't see a TimeSpan datatype called out. Hence assuming it is not supported and which is why you are receiving this error.

    79239-image.png

    Unfortunately this is ADF's limitation, Product team suggest customers to consider changing the Salesforce type (TimeSpan) of that column to the one that is supported by ADF's Salesforce connector. Please refer to this doc for a list of supported Data type mapping for Salesforce : Data type mapping for Salesforce

    If possible try to convert your source column data type to one of ADF supported Data types for Salesforce.

    Additional info: Also please refer to this stackoverflow thread which has a discussion about why it is not possible to convert TimeSpan to a DateTime : https://stackoverflow.com/questions/10276228/timespan-to-datetime-conversion

    Hope this info helps.

    ----------

    Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.