SqlBulkCopy - The given ColumnName does not match up with any column in the source or destination

Question

I'm trying to use SqlBulkCopy to copy data into an SQL database table however it is (wrongly) saying that the columns don't match. They do match. If I use a breakpoint to see the names of the columns being mapped, they're correct. The error message shows the name of the column, and it is correct.

This is my method. I have an identical method that does work and the only difference is where it gets the column names from. The strings containing the column names, however, are EXACTLY identical.

    public static bool ManualMapImport(DataTable dataTable, string table)
    {
        if(dataTable != null)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlBulkCopy import = new SqlBulkCopy(connection);
            import.DestinationTableName = "[" + table + "]";
            foreach (string s in Global.SelectedColumns)
            {                    
            /* The s string variable here is the EXACT same as
               the c.ToString() in the other method below */

                if (ColumnExists(table, s))
                    import.ColumnMappings.Add(s, s); 
                else
                    return false;
            }

            connection.Open();
            import.WriteToServer(dataTable); //Error happens on this line
            connection.Close();

            return true;
        }
        else
        {
            return false;
        }
    }

This is the almost identical, working method:

    public static bool AutoMapImport(DataTable dataTable, string table)
    {
        if (dataTable != null)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlBulkCopy import = new SqlBulkCopy(connection);
            import.DestinationTableName = "[" + table + "]";           
            foreach (DataColumn c in dataTable.Columns)
            {
                if (ColumnExists(table, c.ToString()))
                    import.ColumnMappings.Add(c.ToString(), c.ToString());
                else
                    return false;
            }

            connection.Open();
            import.WriteToServer(dataTable);
            connection.Close();

            return true;
        }
        else
        {
            return false;
        }
    }

If it helps, the column names are: ACT_Code, ACT_Paid, ACT_Name, ACT_Terminal_Code, ACT_TCustom1, ACT_TCustom2. These are exactly the same in the database itself. I'm aware that SqlBulkCopy mappings are case sensitive, and the column names are indeed correct.

This is the error message:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: The given ColumnName 'ACT_Code' does not match up with any column in data source.

Hopefully I'm just missing something obvious here, but I am well and truly lost.

Many thanks.

EDIT: For anyone happening to have the same problem as me, here's how I fixed it.

Instead of having the ManualMapImport() method be a near-clone of AutoMapImport(), I had it loop through the columns of the datatable and change the names, then called AutoMapImport() with the amended datatable, eliminating the need to try and map with plain strings at all.

Solution 1

According to MSDN (here), the DataColumn.ToString() method returns "The Expression value, if the property is set; otherwise, the ColumnName property.".

I've always found the ToString() method to be wonky anyway (can change based on current state/conditions), so I'd recommend using the ColumnName property instead, as that's what you are actually trying to get out of ToString().

---

OK, failing that, then I'd have to guess that this is a problem with case-sensitivity in the names of the columns in the source datatable, as SQLBulkCopy is very case-sensitive even if the SQL DB is not. To address this, I would say that when you check to see if that column exists, then you should return/use the actual string from the datatable's column list itself, rather than using whatever string was passed in. This should be able to fix up any case or accent differences that your ColumnsExist routine might be ignoring.

Solution 2

I had the same problem... The message might seem a bit misleading, as it suggests you didn't perform the correct mapping.

To find the root of the problem I have decided to go step by step in adding table columns and calling the WriteToServer method.

Assuming you have a valid column mapping, you will have to ensure the following between the source DataTable and the destination table:

• The column types and lengths (!) do match
• You have provided a valid value for each non-empty (NOT NULL) destination column

If you don't control your identity column values and would like the SQL Server do this job for you, please make sure not to specify the SqlBulkCopyOptions.KeepIdentity option. In this case you don't add the identity column to your source either.

This should be all for your bulk insert to work. Hope it helps.

Comments

[PorkSausages]

Thanks for the tip, I'll change that, however I'm certain .ToString() isn't the issue here. It's the top method that doesn't use .ToString() that isn't working. The DataColumn.ToString() is working.

[PorkSausages]

It's not that either unfortunately. I already am taking them straight from SQL and populating my lists with them.

[theshinylight]

To illustrate this with an example... my insert was failing because in the source for a column [A] I had a value of "ABCD", while the mapped column [A] in the destination table was char(2).