How to use SqlBulkCopy with nullable columns

Question

I’m having an issue using SqlBulkCopy when nullable columns are involved. It sounds like SqlBulkCopy doesn't know how to deal with nullable columns and throws an illegal size error when it encounters a zero length column. The error is "Received an invalid column length from the bcp client..."

I’m wondering what the best practice is for dealing with this. This seems to be a good forum post describing this issue and how to solve it for reading a csv file.

I think my situation is pretty A-typically and simple. I need to move a unknown amout of data from one database table to another database. The more simple answer for me would be to use SSIS/DTS or linked servers in sql server, however the customer wants the app to do the data movement.

Is there a known work around for this or a better steaming solution for moving data with nullable fields?

//access db
string src_db = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SourceDB.mdb;Jet OLEDB ";
//sql db
string dest_db = @"Data Source=.\TEST;Initial Catalog=testdb;User Id=user;Password=password;";
string sql = "";
OleDbConnection sdb = new OleDbConnection( src_db );
OleDbCommand cmd = new OleDbCommand( sql, sdb );
OleDbDataReader rs = null;

SqlConnection db = new SqlConnection( dest_db );
SqlCommand clear = null;
SqlBulkCopy bulk_load = null;

// Read in the source table
sql = "select * from someTable";
sdb.Open();
cmd = new OleDbCommand( sql, sdb );
rs = cmd.ExecuteReader();

// Import into the destination table
bulk_load = new SqlBulkCopy( db );
bulk_load.DestinationTableName = "test";
bulk_load.WriteToServer( rs );

Answer 1

It should work if you have nullable checked in your destination field in question.

Answer 2

The SqlBulkCopy class can't cope with null fields. It doesn't check if the field is null before trying to calculate it's length. Thus throwing an error.

The post i cited in the question references a implementation of IDataReader that throws DBNull.Value instead of just the null value -- this works around the defect with SqlBulkCopy.