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

Question

I dont know why I am getting the above exception, please someone look at it ....

DataTable DataTable_Time = new DataTable("Star_Schema__Dimension_Time");

DataColumn Sowing_Day = new DataColumn();
Sowing_Day.ColumnName = "Sowing_Day";

DataColumn Sowing_Month= new DataColumn();
Sowing_Month.ColumnName = "Sowing_Month";      

DataColumn Sowing_Year = new DataColumn();
Sowing_Year.ColumnName = "Sowing_Year";

DataColumn Visit_Day= new DataColumn();
Visit_Day.ColumnName = "Visit_Day";

DataColumn Visit_Month = new DataColumn();
Visit_Month.ColumnName = "Visit_Month";

DataColumn Visit_Year = new DataColumn();
Visit_Year.ColumnName = "Visit_Year";

DataColumn Pesticide_spray_day = new DataColumn();
Pesticide_spray_day.ColumnName = "Pesticide_spray_day";

DataColumn Pesticide_spray_Month = new DataColumn();
Pesticide_spray_Month.ColumnName = "Pesticide_spray_Month";

DataColumn Pesticide_spray_Year = new DataColumn();
Pesticide_spray_Year.ColumnName = "Pesticide_spray_Year";

DataTable_Time.Columns.Add(Pesticide_spray_Year);
DataTable_Time.Columns.Add(Sowing_Day);
DataTable_Time.Columns.Add(Sowing_Month);
DataTable_Time.Columns.Add(Sowing_Year);
DataTable_Time.Columns.Add(Visit_Day);
DataTable_Time.Columns.Add(Visit_Month);
DataTable_Time.Columns.Add(Visit_Year);
DataTable_Time.Columns.Add(Pesticide_spray_day);
DataTable_Time.Columns.Add(Pesticide_spray_Month);

adapter.SelectCommand = new SqlCommand(
    "SELECT SowingDate,VisitDate,PesticideSprayDate " +
    "FROM Transformed_Table " + 
    "group by SowingDate,VisitDate,PesticideSprayDate", con);

adapter.SelectCommand.CommandTimeout = 1000;

adapter.Fill(DataSet_DistinctRows, "Star_Schema__Dimension_Time");

DataTable_DistinctRows = DataSet_DistinctRows.Tables["Star_Schema__Dimension_Time"];

int row_number = 0;
int i = 3;

foreach(DataRow row  in DataTable_DistinctRows.Rows)
{
    DataRow flatTableRow = DataTable_Time.NewRow();

    string[] Sarray= Regex.Split(row[0].ToString()," ",RegexOptions.IgnoreCase);
    string[] finalsplit = Regex.Split(Sarray[0], "/", RegexOptions.IgnoreCase);
    string[] Sarray1 = Regex.Split(row[1].ToString(), " ", RegexOptions.IgnoreCase);
    string[] finalsplit2 = Regex.Split(Sarray1[0], "/", RegexOptions.IgnoreCase);
    string[] Sarray2= Regex.Split(row[2].ToString(), " ", RegexOptions.IgnoreCase);
    string[] finalsplit3 = Regex.Split(Sarray2[0], "/", RegexOptions.IgnoreCase);             

    flatTableRow["Sowing_Day"] = int.Parse(finalsplit[0]);
    flatTableRow["Sowing_Month"] = int.Parse(finalsplit[0]);
    flatTableRow["Sowing_Year"] = int.Parse(finalsplit[0]);

    flatTableRow["Visit_Day"] = int.Parse(finalsplit2[0]);
    flatTableRow["Visit_Month"] = int.Parse(finalsplit2[0]);
    flatTableRow["Visit_Year"] = int.Parse(finalsplit2[0]);

    flatTableRow["Pesticide_spray_day"] = int.Parse(finalsplit3[0]);
    flatTableRow["Pesticide_spray_Month"] = int.Parse(finalsplit3[0]);
    flatTableRow["Pesticide_spray_Year"] = int.Parse(finalsplit3[0]);

    DataTable_Time.Rows.Add(flatTableRow);

    i++;
}

con.Open();

using (SqlBulkCopy s = new SqlBulkCopy(con))
{
    s.DestinationTableName = DataTable_Time.TableName;

    foreach (var column in DataTable_Time.Columns)
        s.ColumnMappings.Add(column.ToString(), column.ToString());

    s.BulkCopyTimeout = 500;

    s.WriteToServer(DataTable_Time);
}

I had the same problem. In my case, one of the properties of the class did not have a corresponding column in the table and it was not flagged as ignored. Once I added the column the error was gone. I hope this saves someone's time. — Francisco Goldenstein, Apr 01 '15 at 17:49
Is there a way to get the specific mapping it's complaining about? — Breandán, Oct 14 '15 at 10:32

score 59 · Answer 1 · answered Aug 04 '14 at 03:26

59

It's important to keep in mind sqlBulkCopy columns are case sensitive for some versions of SQL. I think MSSQL 2005. Hope it helps

answered Aug 04 '14 at 03:26

Tonto

2,900
3
25
34

1

Saved me a lot of time. I am using Sql Server2012 and it is case sensitive to, so I assume all versions are case sensitive. – Kazem Nov 15 '19 at 05:13
1

Case sensitivity in SQL depends entirely on the collation used by the database you are targeting. As for the .Net class SqlBulkCopy, it is case sensitive always. – Shooter McGavin Mar 24 '22 at 22:43
SQL Bulk Copy do not provide the miss-matched column name! That is a drawback! – Khalid Bin Sarower Jan 31 '23 at 06:20

score 26 · Answer 2 · edited May 30 '18 at 08:35

One reason is that SqlBulkCopy is case sensitive. Follow steps:

Find your column in the source table by using Contains method in C#.
Once your destination column is matched with the source column, get the index of that column and give its name to SqlBulkCopy.

For Example:

//Get Column from Source table 
string sourceTableQuery = "Select top 1 * from sourceTable";

// i use sql helper for executing query you can use corde sw
DataTable dtSource 
    = SQLHelper.SqlHelper
        .ExecuteDataset(transaction, CommandType.Text, sourceTableQuery)
        .Tables[0];

for (int i = 0; i < destinationTable.Columns.Count; i++)
{
    string destinationColumnName = destinationTable.Columns[i].ToString();

    // check if destination column exists in source table 
    // Contains method is not case sensitive    
    if (dtSource.Columns.Contains(destinationColumnName))
    {
        //Once column matched get its index
        int sourceColumnIndex = dtSource.Columns.IndexOf(destinationColumnName);

        string sourceColumnName = dtSource.Columns[sourceColumnIndex].ToString();

        // give column name of source table rather then destination table 
        // so that it would avoid case sensitivity
        bulkCopy.ColumnMappings.Add(sourceColumnName, sourceColumnName);
    }                               
}

bulkCopy.WriteToServer(destinationTable);
bulkCopy.Close();

Hi, is there any way we can add square braces to the dtSource.Columns, as my datatable contain them ..ex: [Node].. hence its not finding the index due to this..please advise — user1046415, Sep 15 '16 at 15:13
Don't use square brackets. It's going to be a nightmare in your database. I would recommend implementing a method to find/replace them or add in some validation beforehand. — Jacob H, Nov 09 '17 at 16:13

score 5 · Answer 3 · answered Nov 07 '17 at 17:56

5

ENSURE to provide a ColumnMappings
ENSURE all values for source column name are valid and case sensitive.
ENSURE all values for destination column name are valid and case sensitive.
MAKE the source case insensitive

answered Nov 07 '17 at 17:56

Lorena Pita

1,366
1
17
20

4

you should attribute : http://www.sqlbulkcopy-tutorial.net/columnmapping-does-not-match – Mitch Wheat Jun 08 '18 at 10:00
4

Listing these steps isn't particularly useful if you don't show how to perform them. – Tab Alleman Nov 18 '20 at 20:12

score 4 · Answer 4 · answered Jan 05 '20 at 08:44

4

Other than the case sensitivity mentioned in the various answers above. Check that you actually have the same columns and you have not missed any by chance. This happened to one of my colleagues and he was missing one column out of 87 columns. So just double check you have every column from source in your destination as well.

answered Jan 05 '20 at 08:44

Francis Benyah

567
7
11

case sensitivity is for sure something to check... – ransems Oct 27 '21 at 13:33
1

Exactly my problem.. It stupidly says nothing is matched just because I was missing one column of like 35 in the middle. I love being a developer – Ondrej Valenta Dec 08 '22 at 02:49

score 3 · Answer 5 · answered Dec 15 '13 at 23:52

The problem is with the s.ColumnMappings.Add(column.ToString(), column.ToString()); and mapping of your destination and source tables. At least one of the columns in your DataTable doesn't match the destination table.

There are many reasons but one of them can be datatype mismatch. So if you try to insert text into an integer column.

score 3 · Answer 6 · answered Dec 18 '17 at 19:14

3

I had this same error and it turned out to be that I was mapping to a column that did not exist in my destination database. Make sure that your columns do exist if you are going to map them.

answered Dec 18 '17 at 19:14

Cody Patterson

55
6

score 3 · Answer 7 · answered Dec 10 '18 at 11:46

3

In my case, I added a column twice to the ColumnMappings. I removed the duplicate and everything worked ok.

answered Dec 10 '18 at 11:46

CristisS

1,103
1
12
31

score 3 · Answer 8 · answered May 24 '19 at 11:12

3

For other people with the same error (but not applicable in this case, as SqlBulkCopy gets newed up), another cause may be if you are trying to reuse a SqlBulkCopy instance for multiple operations, as the column mappings will persist from operation to operation. In which case, instantiate a new instance of SqlBulkCopy for each operation that requires different column mappings.

answered May 24 '19 at 11:12

Tim Tyler

2,380
2
16
13

This solved it for me. Mappings were correct but was remembering old mappings. Instantiated a new BCP and it worked THANK YOU. – Mattkwish Jan 19 '21 at 18:06

score 1 · Answer 9 · answered Oct 25 '19 at 08:12

I also faced the same issue. In my case, I was getting the Logs table generated from seriLog and it was missing the additional columns in the table creation which was causing the above error.

Once the create the logs table on my own with the required additional columns, this error went away.

score 1 · Answer 10 · answered Jan 16 '20 at 04:12

1

In my case, it was the wrong database name in "Initial Catalog=" in the connection string.

answered Jan 16 '20 at 04:12

abelabbesnabi

1,819
1
16
21

score 1 · Answer 11 · answered Feb 07 '20 at 17:01

1

I ran into the same error. For me, I was accidentally mapping 2 different columns from the source datatable to the same column in the target database table.

answered Feb 07 '20 at 17:01

Icculus018

1,018
11
19

score 1 · Answer 12 · answered Aug 09 '22 at 17:40

1

In our case a vendor changed the case of a json element from “locationID” to “locationId” in their api. We simply changed the column name in the database table (because we can… not recommended)

answered Aug 09 '22 at 17:40

Bill Wheaton

37
5

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

12 Answers12

Linked