Wednesday, 14 April 2010

SqlBulkCopy ColumnMappings Mismatch

"The given ColumnMapping does not match up with any column in the source or destination"

This error caused me more head-scratching than it should have done today. I was using SqlBulkCopy to bulk insert some data into an SQL Server table from a .NET DataTable, something I've done before - it's not rocket science. But I hit this error upon calling WriteToServer and it took me a while (longer than it should have done really!) to track down the problem. The ColumnMappings I'd specified, to map the data columns in my DataTable to the underlying database table columns, all seemed to tally up - each column did exist in the source DataTable and the destination SQL Server table. I was expecting that I'd made a stupid mistake and just couldn't see the wood for the trees.

ColumnMappings are case-sensitive

The problem was because the name I'd given for the destination column name for one of the column mappings, did not match the case of the name of that column in the database. Once I'd eventually identified the problem (an "i" character should have been "I" to match exactly the database schema), it was a bit of a face-palm moment. I don't see any reason why it should be case sensitive and believe it should not matter. A bit more digging led me to this open Microsoft Connect bug report from some years ago.

I won't be tripping over this again!

6 comments:

  1. Thank you very much, you saved a lot of my headache too.

    ReplyDelete
  2. This remains true in SQL Server 2008. How unfortunate!

    ReplyDelete
  3. I, too, have spent a long time head scratching over this one. Having resolved the case sensitivity problem (with very many thanks to this article) my first import worked. And then came the import into a second table. I even went as far as copying the headers from SQL Server Management Studio into Excel but I still got the same error.

    I had been using the same Excel sheet for the second import and just deleted all the data from it before writing the next lot of data. The problem turned out to be that there were fewer columns in the second import so, despite having pressed the delete button excel still thought the additional columns should exist in the export. Solution for me was to delete the entire columns after which I could breathe again

    ReplyDelete
  4. Wow! I spent an hour trying to figure this one out! I thought I was going crazy! Thank you.

    ReplyDelete
  5. OMG... i had this same problem. I couldn't figure it out. I saw this "problem" but people only mentioned sql 2000 and sql 2005, so I thought I was fine because I was using SQL 2008 R2 ... WTH microsoft, make the error message clearer at least... say something like column names are case sensitive or something if you don't plan to fix it.

    ReplyDelete
  6. Same problem. They should have atleast give the specific error message.

    ReplyDelete