How to ignore does not allow DBNull. error on column

Question

Hi Team,

Currently we have requirement to upload Datalake csv file to on promise database, According to when i do a copy activity to sync csv data file to onprime database i have faced below error, but i can use below Alter SQL script to allow Null value, but in the table there have 50+ column affcted this concern and on the table and there have 30+ tables to sync to Database, So is there have a any solution to ignore this issue.

Alter Query for a single column-

ALTER TABLE Jobshipment
ALTER COLUMN JS_TranshipToOtherCFS varchar(30) NULL

error - :
{
"errorCode": "2200",
"message": "Failure happened on 'Sink' side. '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=Column 'JS_ShipmentStatus' does not allow DBNull.Value.,Source=System.Data,'",
"failureType": "UserError",
"target": "Copy data1",
"details": []
}

Answer 1

Since the column datatypes are NOT NULL, it will never allow NULL values.

Either a script has to be written which will generate ALTER TABLE scripts to modify data types to NULL or the process/team which generates CSV should provide some default values instead of blanks

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav