Thanks! That's a big help in debugging.
Data Factory Data Truncation fails doesnt show the column
We have just had a Data truncation error in Azure Data Factory
\" from dbo.table ;"
},
"sink": {
"type": "AzureSqlSink",
"preCopyScript": "TRUNCATE TABLE dbo.table",
"disableMetricsCollection": false
},
"enableStaging": false,
"dataIntegrationUnits": 2
}
But you don't get any information as to what column has caused the problem
We would have to go back to the source table to do a search on maximum characters before going back and changing. This would take a while because we have to get another team involved.
Is there any way we can get this extra information into the error
I think its shipped in SQL Server 2019 possibly but we really need this in Azure Data Factory
3 answers
Sort by: Newest
-
-
HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
2020-09-14T16:04:26.4+00:00 Hello ,
Just wanted to follow up and check again if the suggestion provided helped you resolve the issue .
If you have a better work around or resolution please do share that with the community as it will help other community members .If you have any further question please do let us know .
Thanks & stay safe
Himanshu -
HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
2020-09-02T23:29:01.03+00:00 Hello anonymous user ,
Thanks for posting the question and welcome to Microsoft Q & A .
We think we can do couple of things
To repro the issue we have created a source and sink table . The size of the sink column is deliberately kept less then on the source , so that we can have the failure .
Create table TruncationTest ( id int identity (1,1) ,name varchar(100) ) Create table TruncationTestSink ( id int ,name varchar(5) ) INSERT INTO TruncationTestSink(name) values ('Hubert Wolfstern')
The pipeline will error out with the below message
{
"errorCode": "2200",
"message": "ErrorCode=SqlBulkCopyInvalidColumnLength,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL Bulk Copy failed due to receive an invalid column length from the bcp client.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The service has encountered an error processing your request. Please try again. Error code 4815.\r\nA severe error occurred on the current command. The results, if any, should be discarded.,Source=.Net SqlClient Data Provider,SqlErrorNumber=40197,Class=20,ErrorCode=-2146232060,State=1,Errors=[{Class=20,Number=40197,State=1,Message=The service has encountered an error processing your request. Please try again. Error code 4815.,},{Class=20,Number=0,State=0,Message=A severe error occurred on the current command. The results, if any, should be discarded.,},],'",
"failureType": "UserError",
"target": "Truncation error",
"details": []
}Option 1 .
Use Logging as shown in the below animation , the idea is to log any incompatable row in a blob , you need give the path to a blob .
Option 2
On the source side use the query option as shown below . We can check the column on the sink side and update the query accordingly .
Option 3
You can use the auto create table on the Source side .Let me know how it goes .
Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members