Azure Data Factory Copy Data from CosmosDB to SQL DB Failing - UserErrorInvalidDataValue

Question

I have a task to copy data from CosmosDB to a SQL DB
After about 30mins running it always fails with

Operation on target CosmosToSql failed: ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'Imputed' contains an invalid value '0.10000000149011612'. Cannot convert '0.10000000149011612' to type 'Int64'.,Source=Microsoft.DataTransfer.Common,''Type=System.OverflowException,Message=Value was either too large or too small for an Int64.,Source=mscorlib,'

The Imputed field in Cosmos holds a floating point number- either 0 or a value between 0 and 1

the source is a SELECT c.Imputed FROM c

the sink is a SQL stored procedure. The data type of the Imputed column in the UDT form the table param
is [Imputed] DECIMAL .

Why/where is it trying to convert my float to an Int64?!
Nowhere is the variable defined as an integer.

and how to get around this?

Answer 1

Hello @IanGlass-9566 ,
Thanks for the ask and using the Microsoft Q&A platform .
When you mentioned UDT , did you mean user defined table type ? if yes then at this time this scenario is not supported in ADF store proc , you can only pass native types .
One work around which I can think is pass the columns as varchar and In the proc read the varchar ( I am assuming that it is a JSON ) .
I request you to please upvote the ask here .
Product group does monitor the request and they can plan for the implementation in future .Once you log the feature request you will also be notified on the status of the request . We expect you to keep using this forum and motivate others to do the same . You can always help other community members by answering to their queries .

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members