CODEWITHSUNDEEP
c#sqlbulkcopy
user1056466
user1056466

Reputation: 667

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

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

Upvotes: 43

Views: 118381

Answers (13)

S Nash
S Nash

Reputation: 2499

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.

Upvotes: 0

Bill Wheaton
Bill Wheaton

Reputation: 35

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)

Upvotes: 0

Icculus018
Icculus018

Reputation: 1066

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.

Upvotes: 0

abelabbesnabi
abelabbesnabi

Reputation: 1817

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

Upvotes: 0

Francis Benyah
Francis Benyah

Reputation: 577

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.

Upvotes: 5

Amit Kumar
Amit Kumar

Reputation: 67

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.

Upvotes: 0

Tim Tyler
Tim Tyler

Reputation: 2690

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.

Upvotes: 4

CristisS
CristisS

Reputation: 1163

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

Upvotes: 3

Asad
Asad

Reputation: 409

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

Upvotes: 30

Cody Patterson
Cody Patterson

Reputation: 55

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.

Upvotes: 3

Lorena Pita
Lorena Pita

Reputation: 1516

  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

Upvotes: 6

Tonto
Tonto

Reputation: 3102

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

Upvotes: 67

Tigran
Tigran

Reputation: 882

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.

Upvotes: 3

Related Questions