I recently ran into a customer who wanted to import data into a table that did not allow NULL for one column. However, his data file had some rows that had NULL for that column. He tried the following Bulk Insert command
Msg 4869, Level 16, State 1, Line 1The bulk load failed. Unexpected NULL value in data file row 2, column 1. The destination column (c1) is defined as NOT NULL
Maxerror does not help in this case because the NULL enforcement is done at much later stage and this constraint cannot be disabled. Interestingly, OPENROWSET can easily handle this as follows
insert into customer.dbo.testselect * from OPENROWSET (BULK 'c:\data.txt',FORMATFILE='c:\format.xml') as t1where c1 is not NULL.
Have fun with OPENROWSET.