Using SqlbulkcopycolumnMapping to copy data from datatable to sql database

Question

I'm trying to map the columns from a Datatable to an SQL database.Table, while mapping the columns. But i keep getting this error

"System.InvalidOperationException: 'The given ColumnName 'FobID' does not match up with any column in data source.'"

see the code im using bellow.

using(SqlBulkCopy sqlcopyTFobs = new SqlBulkCopy(destCNS))
                {
                    sqlcopyTFobs.DestinationTableName = "[dbo].[TFobs]";

                    SqlBulkCopyColumnMapping mapFodID = new SqlBulkCopyColumnMapping("FobID", "FobID");
                    sqlcopyTFobs.ColumnMappings.Add(mapFodID);

                    sqlcopyTFobs.WriteToServer(dtTPositions);
                }

If i copy all columns using the following code all data is copied. But i need to only copy certain columns.

using(SqlBulkCopy sqlcopyTFobs = new SqlBulkCopy(destCNS))
                {
                    sqlcopyTFobs.DestinationTableName = "[dbo].[TFobs]";

                    sqlcopyTFobs.WriteToServer(dtTPositions);
                }

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Please be aware that column mapping is case sensitive. Some guidance you can get from MS site. Here is the short paragraph from there

Column mappings define the mapping between data source and the target table.

If mappings are not defined - that is, the ColumnMappings collection is empty - the >columns are mapped implicitly based on ordinal position. For this to work, source and >target schemas must match. If they do not, an InvalidOperationException will be thrown.

If the ColumnMappings collection is not empty, not every column present in the data >source has to be specified. Those not mapped by the collection are ignored.

You can refer to source and target columns by either name or ordinal. You can also mix >by-name and by-ordinal column references in the same mappings collection.

Looks like your mapping is not accurate.

If you are trying to remove some fields from being copied look at this article on stackoverflow Skip some columns in SqlBulkCopy

Using SqlbulkcopycolumnMapping to copy data from datatable to sql database

1 Answers1