none
sqlbulkcopy "The given ColumnMapping does not match up with any column in the source or destination."

    Question

  • Hey everyone hopefully someone might be able to shed some light on an issue I'm having with sqlbulkcopy, I use it to copy a couple of tables between client and server machine via a webservices, basicly just calls a function that has the sqlbulkcopy command on the server.

    What I do is use a dataadapter to pull all the data I require out of the database on the client so "SELECT * FROM tableIneed WHERE clause" and load it into a datatable I than take that datatable put it into a dataset and send it to the function on the server where I extract the datatable and run the following code.

     

    Try

    conn.Open()

    Datasources.Columns.

    Dim bulkcopy As New SqlBulkCopy(conn)

    bulkcopy.ColumnMappings.Clear()

    bulkcopy.DestinationTableName = "tblDataSources"

    bulkcopy.WriteToServer(DataSources)

    conn.Close()

    bulkcopy.Close()

    Catch ex As Exception

    Return ("Code205 : " & ex.ToString)

    End Try

     

    Problem I have is that the webservice returns :

    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.WriteToServerInternal()

    at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)

    at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)

    at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

    at ServerUpload.WellDaq.DataSourcesSync(DataSet DataSourcesDataSet, String JobDate, String Job) in C:\Documents and Settings\Mitch\My Documents\Programming\DataHub\Server\WellDaq.asmx.vb:line 130

    Both the table on the server and the table on the client are identical, I've tried running the entire datatable through a for each row loop and removing the unique identifer tag and setting it to DBNULL so that it would automatically assign a new one and that didnt work. I also tried making the unique identifying column, not unique wich also never worked.

    I have this exact same code working for other datatables in the exact same manner and they work fine just this one table gives me grief, any insight into the cause of this would be greatly appreciated.
    Tuesday, February 27, 2007 12:08 AM

Answers

  • Do you have TimeStamp columns in your table? Or do you have identity columns? If you have identity columns, you need to specify SqlBulkCopyOptions.KeepIdentity?
    Tuesday, February 27, 2007 4:28 AM

All replies

  • Do you have TimeStamp columns in your table? Or do you have identity columns? If you have identity columns, you need to specify SqlBulkCopyOptions.KeepIdentity?
    Tuesday, February 27, 2007 4:28 AM
  • I am performing a SqlBulkCopy (.DBF to Sql Server 2005).... the column mappings are case sensitive on both datasources.
    Tuesday, June 12, 2007 2:59 PM
  • Actually.... I know this sounds weird (or backwards) but the Sql Server 2005 destination columns are case sensitive.

    Tuesday, June 12, 2007 3:17 PM
  • This case-sensitivity thing is sql server setting. Please refer to http://msdn2.microsoft.com/en-US/library/aa197951(sql.80).aspx
    Tuesday, June 12, 2007 4:38 PM
  • The number of fields in .Dbf mast <= of columns in SQL table.
    Use comumnMappings with less fields.


    Wednesday, July 04, 2007 6:21 PM
  • I've encountered the same problem while copy data to SQLSERVER 2000,which is  not case sensitive,
    and I soloved it by using  exactly the same  names of the source data columns names and exactly the
    same names of the destinate table column names when add bulkcopy column mappings

    public void BulkCopyAdapter(string connectionString, string sourceFields, string destinationFields, string destinationTableName, DataTable srcDataTable)
        {
            SqlBulkCopy sbc = new SqlBulkCopy(connectionString);

            string[] arrSrc = sourceFields.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            string[] arrDst = destinationFields.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
           
            //If sourceFields count is not equal to destinationFields count
            //Abort the bulk copy procedure
            if(!arrSrc.Length.Equals(arrDst.Length))
            {
                return;
            }

            //Mapping DataCopy Columns
    // srcColumn has to be the same with source table column names
    //dstColumn has to be the same with destinate table column names
    //It's case sensitive
            string srcColumn = string.Empty;
            string dstColumn = string.Empty;
            for (int index = 0; index < arrSrc.Length; index++)
            {
                srcColumn = arrSrc[index].Trim();
                dstColumn = arrDst[index].Trim().ToUpper();
                sbc.ColumnMappings.Add(srcColumn, dstColumn);
            }
          
            sbc.DestinationTableName = destinationTableName;
            sbc.WriteToServer(srcDataTable);
            sbc.Close();
        }
    Wednesday, March 26, 2008 3:48 AM
  • I have solved this problem ""The given ColumnMapping does not match up with any column in the source or destination.""

    Solution

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Try

                Dim mapping1 As New SqlBulkCopyColumnMapping("Title", "Title")
                Dim mapping2 As New SqlBulkCopyColumnMapping("Director", "Director")
                Dim mapping3 As New SqlBulkCopyColumnMapping("Price", "Price")
                Dim bulkCopy As New SqlBulkCopy(Con)

                myDs = New DataSet
                myDs.ReadXml("E:\WebSite1\XMLFile.xml")
                myDt = myDs.Tables(0)
                Dim myCol As DataColumn

                bulkCopy.DestinationTableName = "myTable"

                bulkCopy.ColumnMappings.Add(mapping1)
                bulkCopy.ColumnMappings.Add(mapping2)
                bulkCopy.ColumnMappings.Add(mapping3)

                bulkCopy.ColumnMappings.Clear()
                For Each myCol In myDt.Columns
                    bulkCopy.ColumnMappings.Add(myCol.ColumnName.Trim(), myCol.ColumnName.Trim())
                Next

                bulkCopy.WriteToServer(myDt)
            Catch ex As Exception
                Response.Write(ex.Message)
            End Try
        End Sub

    Structure of XML is :
    <DvdList>
       <Dvd>
          <Title>The Matrix</Title>
          <Director>Manish</Director>
          <Price>200</Price>
       </Dvd>
       <Dvd>
          <Title>The Math</Title>
          <Director>Sachin</Director>
          <Price>100</Price>
       </Dvd>
    </DvdList>

    Monday, October 06, 2008 10:19 AM
  • I'm using the code above, but have a problemn with compyling at "myDs = New DataSet
                myDs.ReadXml("E:\WebSite1\XMLFile.xml")
                myDt = myDs.Tables(0)
                Dim myCol As DataColumn
    "

    myDt must be declared, and I can't find any sollution for that, hopefully you can help me.

    Wednesday, July 22, 2009 9:04 AM
  • Change

    myDs = New DataSet

    to

    Dim myDs = New DataSet()
    Val Mazur (MVP)

    http://www.xporttools.net

    • Proposed as answer by Naamala Wednesday, November 18, 2009 7:40 PM
    • Edited by VMazurModerator Friday, January 14, 2011 11:55 AM
    Wednesday, July 22, 2009 10:33 AM
  • THis is really big bug in Bulk Insert.  I have table as shown below

    Create Table T(id int,name nvarchar(100))

    Create the same table on other side as shown below

    Create Table T(Id int, Name nvarchar(100))

    Notice that I changed the case to Upper case on first character now run BCP from one site to other side it will throw the same error. 

    In my case i map the source column name to destination name

    bulkcopy.ColumnMappings.Add(source.ColumnName, source.ColumnName);

    This can only be avoided excatly pass the case sensitive column name from both sides.

     

    Monday, January 10, 2011 11:35 PM
  • I got the results I wanted but don't understand how.  I was getting the same error on the column mappings.  I was using the following line of code to set the column mappings.

    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)

    Although the code has been successful in the past it has always been buggy, If I got an error sometimes I have gotten it to work by changing one of the field names to another name in both tables and suddenly it started working even though the old names matched as well.   Today I was mapping 16 fields and couldn't get it to work so I tried the technique above of specifically mapping the columns, I decided to map the first two to give it a try, but when I checked the results all my 16 fields had mapped correctly and the data entered perfectly.  This example is mapping from an Access table to an SQL table.  How did that happen?

    Private Sub InsertViaSqlBulkCopyWithoutTransaction(ByVal AccessData As DataTable)
            Using destinationConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
                destinationConnection.Open()

                Using bulkCopy As New SqlBulkCopy(destinationConnection)
                    bulkCopy.DestinationTableName = "Accfixed"

                    'Define column mappings
                    'Here I was using my old code

                    'bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)

                    'New Code
                    Dim mapping1 As New SqlBulkCopyColumnMapping("ID", "ID")
                    Dim mapping2 As New SqlBulkCopyColumnMapping("Description", "Description")

                    'Next

                    bulkCopy.WriteToServer(AccessData)



                End Using

                destinationConnection.Close()
            End Using
        End Sub

     

     

    Sunday, September 18, 2011 1:23 AM
  • Reason I faced issue is because of case sensitivity of column names in the mapping
    Friday, March 30, 2012 9:37 AM