question

JulioBello-4525 avatar image
0 Votes"
JulioBello-4525 asked JulioBello-4525 commented

System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination

Hi, Everybody!...

I am encountering the following error:

System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination.
at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)...

The exception is thrown upon executing sqlBulkCopy.WriteToServer(dataTable). Please see the following code:

     using (var sqlConnection = new SqlConnection(connectionString))
     {
         sqlConnection.Open();
         using (var sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
         {
             sqlBulkCopy.DestinationTableName = dataTable.TableName;

             foreach (DataColumn column in dataTable.Columns)
                 sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);

             //var i = 0;

             //foreach (DataColumn column in dataTable.Columns)
             //{
             //    SqlBulkCopyColumnMapping columnMapping = sqlBulkCopy.ColumnMappings[i++];
             //    Console.WriteLine("{0}\t{1}\t{2}", column.ColumnName, column.ColumnName == columnMapping.SourceColumn, column.ColumnName == columnMapping.DestinationColumn);
             //}

             sqlBulkCopy.WriteToServer(dataTable);
         }
     }

The commented debug code produces the following output:

98360-image.png



I have spent HOURS on this. What am I missing? What am I doing wrong?

sql-server-generaldotnet-csharpdotnet-runtimedotnet-standard
image.png (44.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered JulioBello-4525 commented

The reason for the error is that some column names are inconsistent even though all the columns are displayed as true.

The following piece of code causes this error:

  foreach (DataColumn column in dataTable.Columns)
           sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);

The data of sqlBulkCopy.ColumnMappings comes from Datatable, and then you use it to compare with the column name in Datatable, it will always be equal.

I think you should compare the column names of the Datatable with the column names of the tables in the database.

I suggest you use the following code to check it:

                     using (SqlCommand command = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'scoretable'", sqlConnection))
                     {
                         DataTable columnNames = new DataTable();
                         columnNames.Load(command.ExecuteReader());
    
                         int i = 0;
                         foreach (DataColumn column in dataTable.Columns)
                         {
                             string columnNameInDB = columnNames.Rows[i++]["COLUMN_NAME"].ToString();
                             Console.WriteLine("{0}\t{1}\t{2}", column.ColumnName, columnNameInDB, columnNameInDB == column.ColumnName);
                         }
                     }

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, TimonYang!

Thank-you for your help!

All the columns were the same except for ONE!... CreatedDateTimeUTC vs. CreatedDataTimeUTC. I can't tell you how many times I "reviewed" BOTH column names... Over and over... Ad nauseum.

I didn't know that ALL of the data of sqlBulkCopy.ColumnMappings came from DataTable. I thought there was some "querying" by the DataTable about the target database table.

This resolves the issue.

I am most truly grateful!






0 Votes 0 ·