Data Factory Data Truncation fails doesnt show the column

Debbie Edwards 521 Reputation points
2020-09-02T09:38:23.08+00:00

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
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,342 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. 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 .

    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


  2. 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

    0 comments No comments

  3. Greg Harwood 1 Reputation point
    2021-04-02T19:45:36.573+00:00

    Thanks! That's a big help in debugging.

    0 comments No comments