The given value of type String from the data source cannot be converted to type nvarchar of the specified target column

{tocify} $title={Table of Contents}

Issue

While working on a POC about moving data from CosmosDB to SQL using Azure Data factory, created a pipeline where I have mapped cosmos db records to sql table columns one to one, pretty simple. 

But when triggered the pipeline, it encountered following error-

"message": "'Type=System.InvalidOperationException,Message=The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.

Why it happened

The error actually is for reason where when your database column length is less as compared to your length of the input.

But the error doesn't specify the exact column, so length of each column has to be checked, and there I found my silly mistake  😏.

While creating the table, I declared the datatype as nvarchar only and didn't specify the length to any of the cloumn i.e. I  didn't specify the the n - nvarchar(n)

n defines the string length that ranges from 1 to 4,000. If you don’t specify the string length, its default value is 1.

What to do

You need to decide beforehand the length of data you expect and accordingly set the length of columns, here as am working on POC I set it to maximum nvarchar(max).

If you have questions or suggestions, feel free to do in comments section below !!!

Do share if you find this helpful .......

 

                          Knowledge Sharing is Caring !!!!!!

Learn More about some more Azure Data Factory errors

• Cannot find the object "xxx" because it does not exist or you do not have permissions.
• The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'.
• ErrorCode=UserErrorInvalidColumnMappingColumnCountMismatch,Message=The column count in column mappings is more than the count in source/sink table.
• ErrorCode=UserErrorInvalidColumnMappingColumnNotFound,Column 'xxx' specified in column mapping cannot be found in source data.
• The path 'xxx' specified in the add operation already exists. Please specify a new path
• Cannot create Sql Source. Error: The value of the property 'Value' is invalid for the stored procedure parameter 'XXX'
• Cannot connect to SQL Database:'XXX' . Verify that the instance name is correct
• Cannot open server 'xxx' requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the server
• Operation on target Copy data failed: Failure happened on 'Source' side