Converting to Oracle version 2 linked service in ADF is erroring converting string to Nvarchar

Question

Converting to Oracle version 2 linked service in ADF is erroring converting string to Nvarchar

Jeffrey Miller - SA 0

I'm converting to the Oracle version 2 linked service and nearly all of my jobs work except for a few which give me the error

"Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''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.,Source=System.Data,''Type=System.InvalidOperationException,Message=String or binary data would be truncated.,Source=System.Data,'"

I have the linked service set to support V1 data types, If I remove that I get the "cannot convert Decimal" error.

Thanks for your help

Chandra Boorla 14,510 Reputation points Microsoft External Staff Moderator

2025-06-16T18:57:05.1566667+00:00
@Jeffrey Miller - SA

Thanks for your detailed post. The error you're encountering "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column." "String or binary data would be truncated." indicates that some string values from your Oracle source are larger than the size defined in the corresponding nvarchar columns in your sink (target) database. This typically results in a truncation error when trying to write the data.

Since you’ve enabled V1 data type compatibility in the Oracle V2 linked service, it’s attempting to map Oracle data types similarly to the older connector. While that helps avoid certain issues (like the decimal conversion error you mentioned), it may cause string size mismatches if your Oracle VARCHAR2 columns contain large values.

Here are few troubleshooting steps:

Adjust the Sink Column Size - If the target nvarchar column is too short, consider altering the column to increase its size.

Truncate Long Strings - You can apply truncation using a Derived Column in ADF Mapping Data Flow or modify the query to cast the string with a fixed length using something like CAST(column_name AS NVARCHAR2(4000)).

Validate and Clean Input Data - Make sure the data in your source system is within expected limits.

For additional information, please refer: Solving the “The given value of type String from the data source cannot be converted to type nvarchar of the specified target column” issue

For more details, please refer the following documentation for useful insights:

Error code: SqlOperationFailed

Error message: String or binary data is truncated

Disclaimer: This response contains a reference to a third-party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Jeffrey Miller - SA 0 Reputation points

2025-06-16T19:11:02.6233333+00:00

I'm dropping and rebuilding the table every time. Would the Bulk insert option help ADF size the new table appropriately?
Chandra Boorla 14,510 Reputation points Microsoft External Staff Moderator

2025-06-16T19:18:58.2266667+00:00
@Jeffrey Miller - SA

Thanks for the additional detail.

If you're dropping and recreating the table each time, using the Bulk Insert option in Azure Data Factory with "Auto Create Table" can help ADF infer the schema. However, please note:

ADF may not always size nvarchar columns correctly, for example, it may default to nvarchar(255) or similar.

If your source contains longer strings, this could still lead to the truncation error you're seeing.

Suggested approach:

After auto-creating the table, review and adjust column lengths manually, especially for string columns.

Alternatively, predefine the table schema with appropriate nvarchar lengths to avoid runtime issues.

If needed, use a Data Flow to apply substring() on string fields before writing to the sink.

I hope this information helps.
Jeffrey Miller - SA 0 Reputation points

2025-06-17T08:07:04.0133333+00:00

I tried using the substring on the long text fields before writing to the sync, with no luck. Finally, I decided to turn on the Fault Tolerance and skip invalid rows. I also set up logging and found it was actually numbers with several characters after the Decimal. I already had set the new linked service to use the V1 Conversions which had always worked and had the Convert Decimal to Int checked, but I guess that's not exactly working. I used the Round function in my source query to resolve the issue.

1 answer

Your answer

Jeffrey Miller - SA 0 Reputation points

2025-06-16T19:11:02.6233333+00:00

I'm dropping and rebuilding the table every time. Would the Bulk insert option help ADF size the new table appropriately?
Chandra Boorla 14,510 Reputation points Microsoft External Staff Moderator

2025-06-16T19:18:58.2266667+00:00

@Jeffrey Miller - SA

Thanks for the additional detail.

If you're dropping and recreating the table each time, using the Bulk Insert option in Azure Data Factory with "Auto Create Table" can help ADF infer the schema. However, please note:

ADF may not always size nvarchar columns correctly, for example, it may default to nvarchar(255) or similar.

If your source contains longer strings, this could still lead to the truncation error you're seeing.

Suggested approach:

After auto-creating the table, review and adjust column lengths manually, especially for string columns.

Alternatively, predefine the table schema with appropriate nvarchar lengths to avoid runtime issues.

If needed, use a Data Flow to apply substring() on string fields before writing to the sink.

I hope this information helps.
Jeffrey Miller - SA 0 Reputation points

2025-06-17T08:07:04.0133333+00:00

I tried using the substring on the long text fields before writing to the sync, with no luck. Finally, I decided to turn on the Fault Tolerance and skip invalid rows. I also set up logging and found it was actually numbers with several characters after the Decimal. I already had set the new linked service to use the V1 Conversions which had always worked and had the Convert Decimal to Int checked, but I guess that's not exactly working. I used the Round function in my source query to resolve the issue.

Answer 1

@Jeffrey Miller - SA

Thanks for the update, and I appreciate you taking the time to dig into the issue and share your findings.

It’s interesting to hear that the root cause turned out to be numeric values with extra precision after the decimal, rather than the long text fields initially suspected. Even with supportV1DataTypes = true and the “Convert Decimal to Int” option enabled, the behavior can sometimes be inconsistent depending on how the data is represented in the source.

Using the ROUND() function in your source query is a great workaround, it ensures that the values align with the expected target data type and avoids runtime conversion issues.

Also, enabling Fault Tolerance and logging was a smart move. It’s a great way to isolate problematic rows and gain clarity on data-level mismatches.

Thank you.

Share via

Converting to Oracle version 2 linked service in ADF is erroring converting string to Nvarchar

1 answer

Your answer