How to use SqlBulkCopy with null columns
I have a problem using SqlBulkCopy where null columns are involved. It looks like SqlBulkCopy doesn't know how to handle null columns and throws an illegal size error when a null-length column is encountered. Error "Received invalid column length from bcp client ..."
I am wondering what is the best way to solve this problem. This seems to be a good forum post describing the problem and its solution for reading the csv file.
I think my situation is pretty typical and simple. I need to transfer unknown data from one database table to another database. A simpler answer for me would be to use SSIS / DTS or linked servers on sql server, however the client wants the application to do 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 );
source to share
The SqlBulkCopy class cannot handle null fields. It does not check if the field is null before trying to calculate its length. So an error is thrown.
The post I quoted in the question refers to an implementation of IDataReader that produces DBNull.Value instead of null - this works around a defect with SqlBulkCopy.
source to share