question

DebbieEdwards-9837 avatar image
0 Votes"
DebbieEdwards-9837 asked GregHarwood-4248 answered

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

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT commented

Hello @DebbieEdwards-9837 ,

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 .


22210-trucation.gif


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 .

22190-2020-09-02-16-21-36.png


Option 3
You can use the auto create table on the Source side .

22296-100.png


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








trucation.gif (574.8 KiB)
100.png (22.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello ,

Just wanted to follow up and check 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

1 Vote 1 ·
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GregHarwood-4248 avatar image
0 Votes"
GregHarwood-4248 answered

Thanks! That's a big help in debugging.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.