Stephen A. Fuqua (SAF) is a Bahá'í, software developer, and conservation and interfaith advocate in the DFW area of Texas.

Explicit Column Mappings for SqlBulkCopy

January 18, 2011

We went back and forth: "it doesn't work," I reported. "But it works on the dev server," says the developer. Can we both be right? I rebuild the code from my machine, where the unit test is not passing (due to an attempt to insert a value into a calculated column). Install on the dev server. Works without error.

Next day I'm reviewing another application that uses SqlBulkCopy. Another error. This time, data for one field is being inserted into another field. All along I suspected that the explicit ColumnMappings were needed. Now with a second data point, I went to look at the schema.

• On the dev server, the first table had the calculated column as the last field, but on mine the last two fields were flipped.
• An older version of the second table's create script did not have an identity field. It exists in production, but if the corrected script were not loaded on the developer's unit testing database, then the field would be missing. It is the first field in the table.

So, although I see nothing stating this in the documentation, it appears that the order of the columns in the destination table matters. In fact, without explicitly mapping columns using the ColumnMappings property, the source and destination are matched by index, rather than column name. Thus if Table1 is defined as (Field1 varchar(10), Field2 varchar(10)) and it is loaded using Object2 { string Field2; string Field1 }, then the values loaded into the database will be flipped, Object2.Field2 -> Table1.Field1. Add an explicit mapping and now it works.