44

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); }
Improve this question
2
  • 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. Commented Apr 1, 2015 at 17:49
  • 14
    Is there a way to get the specific mapping it's complaining about? Commented Oct 14, 2015 at 10:32

13 Answers 13

Reset to default
69

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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.
SQL Bulk Copy do not provide the miss-matched column name! That is a drawback!
31

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

  1. Find your column in the source table by using Contains method in C#.
  2. 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();
Improve this answer

2 Comments

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
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.
6

  1. ENSURE to provide a ColumnMappings

  2. ENSURE all values for source column name are valid and case sensitive.

  3. ENSURE all values for destination column name are valid and case sensitive.

  4. MAKE the source case insensitive

Improve this answer

2 Comments

you should attribute : sqlbulkcopy-tutorial.net/columnmapping-does-not-match
Listing these steps isn't particularly useful if you don't show how to perform them.
5

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.

Improve this answer

2 Comments

case sensitivity is for sure something to check...
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
4

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.

Improve this answer

2 Comments

This solved it for me. Mappings were correct but was remembering old mappings. Instantiated a new BCP and it worked THANK YOU.
correct, I was using same transition for different database table
3

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.

Improve this answer

Comments

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.

Improve this answer

Comments

3

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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)

Improve this answer

Comments

0

In my case I was copying data from Excel Worksheet to sql server.

None of above suggestions worked for me. After 1 hour debugging in turned out that I had a space after the column name in Excel! Very hard to debug this as seeing that space in Excel was really hard.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.