SqlBulkCopy problem

Question

i use SqlBulkCopy to insert data from OleDbDataReader (contains data from xls) to mssql-2005 i have a cloumn on the OleDbDataReader that contains number stored as text (in the xls)

when i look into the mssql data i see null in that column all other columns are move ok.

link text

Answer 1

you need to map columns like in the code...

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection))
 {
 bulkCopy.ColumnMappings.Add("ID", "ID");
 bulkCopy.ColumnMappings.Add("Email", "Email");
 bulkCopy.DestinationTableName = "tableName";
 bulkCopy.WriteToServer(ExcelReader);
 }

Answer 2

When one bulk copies from SQL server to SQL Server, the source is able to indicate very well the data types to the destination. With Excel, the source makes guesses about the data type. You may have the Excel formatted as text, but the source (ODBC?) might look at the first 50 rows and guess that it's a numeric datatype. Even if the column names match, if the data types are different I suspect the column doesn't get populated.