How to fix OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert]' error?

Muhammad Tayyab Majeed 0 Reputation points
2024-09-25T12:40:36.9766667+00:00

I'm running a synapse pipeline to copy data from ODBC database to an Azure SQL Database. However, I'm seeing an error that suggests there might be invalid data causing the pipeline to fail. I'm auto-creating the table at the sink, so theoretically the schema should be transferred correctly. I've searched the MS documentation but haven't found anything helpful. Can someone provide assistance in fixing this error? The error details are as follows:

{ "errorCode": "2200", "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.Data.SqlClient.SqlException,Message=OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].GROSS_AMOUNT'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=7339,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=7339,State=1,Message=OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].GROSS_AMOUNT'.,},],'", "failureType": "UserError", "target": "Copy data", "details": [] }

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,903 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sina Salam 10,261 Reputation points
    2024-09-25T14:49:10.11+00:00

    Hello Muhammad Tayyab Majeed,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    I understand that you would like to fix OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert]' error.

    Regarding the error code and your explanations, this is a generic error in Azure SQL Database that center around data mismatches that ODBC could not understand, kindly check the below one after the other to fix them.

    • You're attempting to insert string data into a numeric column and/or vice versal.
    • There is a mismatch data types in the GROSS_AMOUNT column.
    • Remove NULL values or default values in your source data and clean your data.
    • In your bulk insert options, review settings and consider adjusting them to handle exceptions in a better form.
    • Implement more error handling and logging for more monitoring of incidents.

    I hope this is helpful! Do not hesitate to let me know if you have any other questions.

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.