The given ColumnMapping does not match up with any

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);
}

Answer 1

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

Answer 2

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();

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.