Column 'ABC' does not allow DBNull.Value, but no null values in data source.

Question

Column 'ABC' does not allow DBNull.Value, but no null values in data source.

Mike McGuinness 26

How does one troubleshoot this issue?

"Operation on target XYZ CSV failed: 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.InvalidOperationException,Message=Column 'ABC' does not allow DBNull.Value.,Source=System.Data,'

I have:

Verified that every row in the data source has a value for that column
Verified that "First row as header" is turned on for the source
I have turned on fault tolerance and still get the same error. 0/399,112 records imported
Have turned on logging for the copy activity and get nothing in the log except it says it picked up and processed the file.
Verified that all other non-null fields always have data

Source is CSV, target is Azure SQL. Same process works for many other similar files without issue.

How do you troubleshoot this with such vague error details that doesn't even point to a specific row?

MartinJaffer-MSFT 26,236 Reputation points

2022-01-31T18:03:08.07+00:00

Hello @Mike McGuinness and welcome to Microsoft Q&A.

I appreciate how you checked so many points. There is one point you missed, though. Are there empty-strings? Or did you set the null value representation to something that occurs in your data? The default value is empty string.
Mike McGuinness 26 Reputation points

2022-01-31T18:13:39.2+00:00
Thanks for your reply @MartinJaffer-MSFT . Per my post, the first thing I did was:

Verified that every row in the data source has a value for that column

So there are no nulls in that column in the data source. I have not changed the "Null value" setting. So I'm not sure if I'm misunderstanding something in your reply. And I'm not sure how to troubleshoot this. Is there some way to get useful logging/debugging details like you would get in an SSIS package?

To add to this post, I modified the SQL Azure destination table to allow nulls in that column, but now the import gets a timeout error after 2:12, and I can't figure out how to increase the timeout as it doesn't seem to be caused by the timeout parameters on the Copy Data task. This isn't an ideal option anyway as killing the key field probably hurts upsert performance significantly.

I have created about a dozen similar copy data tasks for other CSV files from the same vendor, and they all work. Only difference I can find is that this one has >330K rows, whereas the other files only have 1K-10K rows.

Any other thoughts on how to troubleshoot?
MartinJaffer-MSFT 26,236 Reputation points

2022-01-31T19:51:19.823+00:00

Wow that was a fast response @Mike McGuinness !

Hmm, well given that 0, none of the records were written, this indicates the problem is not 'just some rows'. The next thing to double-check would be the mapping or whether first row is header. A mis-map could explain why the problem applies to all rows.
On a similar subject, are you using the "additional columns" feature in the Copy Activity source?

I just did a look for similar sounding known issues centered around the error message, and have not found anything so far.

If all else fails, it is still worth adjusting the settings like write batch size or parallelism to test whether this is related to data size or transmission or timeout. I do not feel it is likely, but I like to cover all possibilities.
Mike McGuinness 26 Reputation points

2022-02-01T00:43:25.537+00:00
Thanks @MartinJaffer-MSFT , I'm still not making any progress. I checked settings again.

First row as header is turned on, and the file has a header row.

All of the columns from source to sink are mapped correctly, the source data shows in correct columns in preview, and the problem column has data for all rows.

I am not using "additional columns".

I tried setting "Write batch timeout" to 10.23:55:55, "Write batch size" to 100 and "Degree of copy parallelism" to 1. With the sink set to allow null values, I get the error below:

System.Data.SqlClient.SqlException,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=-2,Class=11,ErrorCode=-2146232060,State=0,Errors=Class=11,Number=-2,State=0

It shows 399,112 "Interim rows written", but zero rows in the sink. "Writing to sink" times out at 2:13, total duration 3:09.

Is there some way to debug this?
Mike McGuinness 26 Reputation points

2022-02-01T17:21:52.73+00:00

Update:

Threw the file into SSIS and imported 330K+ records into a copy of the table in a local SQL DB with zero issues.

Pointed SSIS at SQL Azure, and that worked fine too.

Deleted and re-created the pipeline, source and sink, and still getting the same error.

Column 'CFISecPricingID' does not allow DBNull.Value

The column does not have any NULL values.

Any other troubleshooting ideas @MartinJaffer-MSFT ?
MartinJaffer-MSFT 26,236 Reputation points

2022-02-01T18:53:11.517+00:00

@Mike McGuinness
I was able to find a lot more information on this new error message. The direct cause of this error message is:

By default, the SQL sink client sets a 2 hour timeout on the connection when writing to the database. When the batch being written takes more than 2 hours to complete, the connection times out before completion and returns an error.

The related resource suggests:

Set a Write batch size on the Sink settings so that the write is broken up into multiple batches that should complete within 2 hours each. 10,000 is a good starting number. Alternatively, you can set the Write batch timeout to a longer time than 02:00:00.

Given that your write batch size is 100, much much less than 10,000, yet it timed out, I speculate that something else is hoarding your server's resources. 400,000 rows is not a large amount (unless you have something like 100 columns). Are there any other processes with heavy I/O, or otherwise occupying the server's resources?

I'll look around for more info, but I may have to refer you to a support ticket for more in-depth investigation.
Mike McGuinness 26 Reputation points

2022-02-01T19:06:01.027+00:00

Hi @MartinJaffer-MSFT , it's a staging database. Nothing is running against it except the pipeline I'm trying to debug. And it times out in 2 minutes, not 2 hours. Changing the "Write batch size" and "Write batch timeout" have no effect. CPU usage goes from 0% to 100% for 2 minutes, then task fails and CPU goes back to 0%.

It appears that this issue is related to Upsert and TempDB.

When I truncate the table and run the copy in insert mode, it completes in 1 minute.

2 answers

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-01-31T18:03:08.07+00:00

Hello @Mike McGuinness and welcome to Microsoft Q&A.

I appreciate how you checked so many points. There is one point you missed, though. Are there empty-strings? Or did you set the null value representation to something that occurs in your data? The default value is empty string.
Mike McGuinness 26 Reputation points

2022-01-31T18:13:39.2+00:00

Thanks for your reply @MartinJaffer-MSFT . Per my post, the first thing I did was:

Verified that every row in the data source has a value for that column

So there are no nulls in that column in the data source. I have not changed the "Null value" setting. So I'm not sure if I'm misunderstanding something in your reply. And I'm not sure how to troubleshoot this. Is there some way to get useful logging/debugging details like you would get in an SSIS package?

To add to this post, I modified the SQL Azure destination table to allow nulls in that column, but now the import gets a timeout error after 2:12, and I can't figure out how to increase the timeout as it doesn't seem to be caused by the timeout parameters on the Copy Data task. This isn't an ideal option anyway as killing the key field probably hurts upsert performance significantly.

I have created about a dozen similar copy data tasks for other CSV files from the same vendor, and they all work. Only difference I can find is that this one has >330K rows, whereas the other files only have 1K-10K rows.

Any other thoughts on how to troubleshoot?
MartinJaffer-MSFT 26,236 Reputation points

2022-01-31T19:51:19.823+00:00

Wow that was a fast response @Mike McGuinness !

Hmm, well given that 0, none of the records were written, this indicates the problem is not 'just some rows'. The next thing to double-check would be the mapping or whether first row is header. A mis-map could explain why the problem applies to all rows.
On a similar subject, are you using the "additional columns" feature in the Copy Activity source?

I just did a look for similar sounding known issues centered around the error message, and have not found anything so far.

If all else fails, it is still worth adjusting the settings like write batch size or parallelism to test whether this is related to data size or transmission or timeout. I do not feel it is likely, but I like to cover all possibilities.
Mike McGuinness 26 Reputation points

2022-02-01T00:43:25.537+00:00

Thanks @MartinJaffer-MSFT , I'm still not making any progress. I checked settings again.

First row as header is turned on, and the file has a header row.

All of the columns from source to sink are mapped correctly, the source data shows in correct columns in preview, and the problem column has data for all rows.

I am not using "additional columns".

I tried setting "Write batch timeout" to 10.23:55:55, "Write batch size" to 100 and "Degree of copy parallelism" to 1. With the sink set to allow null values, I get the error below:

System.Data.SqlClient.SqlException,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=-2,Class=11,ErrorCode=-2146232060,State=0,Errors=Class=11,Number=-2,State=0

It shows 399,112 "Interim rows written", but zero rows in the sink. "Writing to sink" times out at 2:13, total duration 3:09.

Is there some way to debug this?
Mike McGuinness 26 Reputation points

2022-02-01T17:21:52.73+00:00

Update:

Threw the file into SSIS and imported 330K+ records into a copy of the table in a local SQL DB with zero issues.

Pointed SSIS at SQL Azure, and that worked fine too.

Deleted and re-created the pipeline, source and sink, and still getting the same error.

Column 'CFISecPricingID' does not allow DBNull.Value

The column does not have any NULL values.

Any other troubleshooting ideas @MartinJaffer-MSFT ?
MartinJaffer-MSFT 26,236 Reputation points

2022-02-01T18:53:11.517+00:00

@Mike McGuinness
I was able to find a lot more information on this new error message. The direct cause of this error message is:

By default, the SQL sink client sets a 2 hour timeout on the connection when writing to the database. When the batch being written takes more than 2 hours to complete, the connection times out before completion and returns an error.

The related resource suggests:

Set a Write batch size on the Sink settings so that the write is broken up into multiple batches that should complete within 2 hours each. 10,000 is a good starting number. Alternatively, you can set the Write batch timeout to a longer time than 02:00:00.

Given that your write batch size is 100, much much less than 10,000, yet it timed out, I speculate that something else is hoarding your server's resources. 400,000 rows is not a large amount (unless you have something like 100 columns). Are there any other processes with heavy I/O, or otherwise occupying the server's resources?

I'll look around for more info, but I may have to refer you to a support ticket for more in-depth investigation.
Mike McGuinness 26 Reputation points

2022-02-01T19:06:01.027+00:00

Hi @MartinJaffer-MSFT , it's a staging database. Nothing is running against it except the pipeline I'm trying to debug. And it times out in 2 minutes, not 2 hours. Changing the "Write batch size" and "Write batch timeout" have no effect. CPU usage goes from 0% to 100% for 2 minutes, then task fails and CPU goes back to 0%.

It appears that this issue is related to Upsert and TempDB.

When I truncate the table and run the copy in insert mode, it completes in 1 minute.

Answer 1

Mike McGuinness 26

FYI, I found a solution to the error bellow when importing a large CSV file. The solution was to change the source encoding from UTF-8 to US-ASCII.

I'm still getting a timeout error on the sync, so I'll open a new question for that. The error below is now resolved:

"Operation on target XYZ CSV failed: 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.InvalidOperationException,Message=Column 'ABC' does not allow DBNull.Value.,Source=System.Data

MartinJaffer-MSFT 26,236 Reputation points

2022-02-01T19:22:11.173+00:00

Thank you for updating with the solution. So the cause was setting the encoding of the source dataset to UTF-8 when the actual data was in ASCII? Very interesting. If I can repro that, I'll add it to our knowledge base.

I know that ASCII characters are each 1 byte long. I'm a little hazy on the details, but I know UTF-8 can be two bytes long, and uses the high-order bit to indicate another byte is expected.

Doing a quick web search turned up:

Why is the null character encoded as 2 bytes in UTF-8?

In UTF-8 it is a single zero byte. Nevertheless, in Modified UTF-8 the null character is encoded as two bytes: 0xC0, 0x80. This lets the byte with the value of zero to be used as a string terminator, because currently it's not used for any character.

However UTF-8 is supposed to be backwards compatible with ASCII, so it is a bit strange.
Mike McGuinness 26 Reputation points

2022-02-01T20:45:23.477+00:00

Thanks for your input on this Martin.

SSIS treated the file as ASCII by default, but ADF defaulted to UTF-8. Not sure why, but that seems to be where the issue lies.

Oddly, I have 22 other ASCII CSV sources that have defaulted to UTF-8, and the rest of them all work, so must be some character combination in the one file that confuses the parser.

It would be great if ADF could highlight what it thinks is the problem row/column in the error message so that it might be possible to pinpoint problem character codes.

Answer 2

I am running into exact same problem as the OP. I'm testing with only 50 rows, so I generate the dataset object, then write it to a text file in a CSV format (different separator) and load it into Excel where I can easily confirm that the data all looks correct. On the particular column that is being reported, EVERY row has a valid string value. I've checked that the number of rows in the Dataset is correct in order to ensure that there's not somehow a blank/empty row.

I really wish the error message would have reported what row the problem happened with, but this is a bogus error anyway so chasing it down directly is probably a waste of time. It just means the problem is somewhere else and SqlBulkCopy is unable to report the REAL error.

Share via

Column 'ABC' does not allow DBNull.Value, but no null values in data source.

2 answers

Your answer