SqlBulkCopy problem

asp.net c# sqlbulkcopy sql-server-2005

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

Accepted Answer

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);
        }

Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why