Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,134 questions
Would you do the ALTER TABLE as a pre-copy script in the ADF copy task?
Error on Data Types string to nvarchar when copying data from Netsuite to Azure SQL database in ADF
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(38.4, 85%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECC%3C/text%3E%3C/svg%3E)
Chantelle Carley
0
Reputation points
I am trying to copy a table from Netsuite to an Azure SQL database using an ODBC connector from Netsuite with SuiteAnalytics Connect. Most of the tables are coming across fine, there are a couple giving this error. I realize it is something to do with the mapping but I am unable to manually update the data type in the mapping. Any ideas on how to fix this would be very much appreciated!
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,'
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 48%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Harishga 5,910 Reputation points Microsoft Vendor2024-01-12T08:50:15.0833333+00:00 Hi @Chantelle Carley
Welcome to Microsoft Q&A platform and thanks for posting your question here.
The error message suggests that the data type in the mapping is incorrect and the given value of type String from the data source cannot be converted to type nvarchar of the specified target column. This error is caused when the length of the data being inserted is longer than the length of the column in the target table. To fix this, you can try increasing the length of the target column in the Azure SQL database or modifying the data type of the source column in Netsuite to match the target column in Azure SQL database.I hope this helps! Let me know if you have any other question.
-
Chantelle Carley 0 Reputation points
2024-01-12T15:43:34.2333333+00:00 Thank you for your reply. How do I determine which column is causing the error and then how do I change the length in the target database?
-
Harishga 5,910 Reputation points Microsoft Vendor
2024-01-16T08:23:00.8066667+00:00 Hi @Chantelle Carley
To determine which column is causing the error, you can check the error message for the name of the column. The error message you provided indicates that the error occurred on the ‘Sink’ side and the given value of type String from the data source cannot be converted to type nvarchar of the specified target column. You can use this information to identify the column in the target table that is causing the error. To change the length of a column in Azure SQL database, you can use the ALTER TABLE statement.Reference
https://video2.skills-academy.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16
I hope this information helps you. Let me know if you have any further questions or concerns. -
susmit sarker 0 Reputation points2024-07-03T20:40:19.1133333+00:00 @Harishga Question was "How do I determine which column is causing the error" - your answer was "To determine which column is causing the error, you can check the error message for the name of the column. The error message you provided indicates that the error occurred on the ‘Sink’ side". Yes, the issue happens on the Sink Side; but how to determine which column/field is causing the issue from this message? Thank you.
"Code": 22301,"Message": "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,'"
Sign in to comment
1 answer
Sort by: Most helpful
-
Chantelle Carley 0 Reputation points
2024-01-16T16:00:48.4866667+00:00 -
Harishga 5,910 Reputation points Microsoft Vendor
2024-01-17T11:20:40.09+00:00 Hi @Chantelle Carley
Yes, you can use the ALTER TABLE statement as a pre-copy script in the ADF copy task to modify the length of the target column. You can include the ALTER TABLE statement in the pre-copy script section of the copy activity in your ADF pipeline. This will ensure that the target table is modified before the data is copied from Netsuite to Azure SQL database. -
Harishga 5,910 Reputation points Microsoft Vendor
2024-01-18T04:18:36.81+00:00 Hi @Chantelle Carley
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. -
Harishga 5,910 Reputation points Microsoft Vendor
2024-01-19T03:30:43.8333333+00:00 Hi @Chantelle Carley
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment -