SqlBulkCopy Doesn't Work

asp.net c# sqlbulkcopy

Question

I possessDataSet filled in using an Excel Sheet. I intended to insert records using SQLBulk Copy.Lead_Hdr place whereLeadId is PK.

While running the code below, I am getting the following error:

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

string ConStr=ConfigurationManager.ConnectionStrings["ConStr"].ToString();

using (SqlBulkCopy s = new SqlBulkCopy(ConStr,SqlBulkCopyOptions.KeepIdentity))
{
    if (MySql.State==ConnectionState.Closed)
    {
        MySql.Open();
    }

    s.DestinationTableName = "PCRM_Lead_Hdr";
    s.NotifyAfter = 10000;

    #region Comment
    s.ColumnMappings.Clear();

    #region ColumnMapping
    s.ColumnMappings.Add("ClientID", "ClientID");
    s.ColumnMappings.Add("LeadID", "LeadID");
    s.ColumnMappings.Add("Company_Name", "Company_Name");
    s.ColumnMappings.Add("Website", "Website");
    s.ColumnMappings.Add("EmployeeCount", "EmployeeCount");
    s.ColumnMappings.Add("Revenue", "Revenue");
    s.ColumnMappings.Add("Address", "Address");
    s.ColumnMappings.Add("City", "City");

    s.ColumnMappings.Add("State", "State");
    s.ColumnMappings.Add("ZipCode", "ZipCode");
    s.ColumnMappings.Add("CountryId", "CountryId");

    s.ColumnMappings.Add("Phone", "Phone");
    s.ColumnMappings.Add("Fax", "Fax");
    s.ColumnMappings.Add("TimeZone", "TimeZone");
    s.ColumnMappings.Add("SicNo", "SicNo");
    s.ColumnMappings.Add("SicDesc", "SicDesc");

    s.ColumnMappings.Add("SourceID", "SourceID");
    s.ColumnMappings.Add("ResearchAnalysis", "ResearchAnalysis");
    s.ColumnMappings.Add("BasketID", "BasketID");
    s.ColumnMappings.Add("PipeLineStatusId", "PipeLineStatusId");

    s.ColumnMappings.Add("SurveyId", "SurveyId");
    s.ColumnMappings.Add("NextCallDate", "NextCallDate");
    s.ColumnMappings.Add("CurrentRecStatus", "CurrentRecStatus");
    s.ColumnMappings.Add("AssignedUserId", "AssignedUserId");
    s.ColumnMappings.Add("AssignedDate", "AssignedDate");
    s.ColumnMappings.Add("ToValueAmt", "ToValueAmt");
    s.ColumnMappings.Add("Remove", "Remove");
    s.ColumnMappings.Add("Release", "Release");

    s.ColumnMappings.Add("Insert_Date", "Insert_Date");
    s.ColumnMappings.Add("Insert_By", "Insert_By");
    s.ColumnMappings.Add("Updated_Date", "Updated_Date");
    s.ColumnMappings.Add("Updated_By", "Updated_By");

    #endregion
    #endregion

    s.WriteToServer(sourceTable);

    s.Close();

    MySql.Close();
}
1
20
4/23/2014 11:06:02 AM

Accepted Answer

So, is it accurate? The column names are there on both sides, right?

I haven't really bothered with mappings, to be honest. I like to have a staging table that resembles the input on the server since I want to keep things simple.SqlBulkCopy ultimately execute a stored method to transfer the table from the staging table into the real table; benefits:

  • no problems with corrupting live data if the import fails at any stage
  • I can create a transaction that just affects the SPROC.
  • Knowing that the SPROC will be recorded allows me to have the bcp run without logging.
  • It is easy;-p (no messing with mappings)

Last but not least, if you are working with massive data, you may improve throughput byIDataReader (Because this is a streaming API, as opposed toDataTable is an API with buffers). For instance, I often use CsvReader as the source for a SqlBulkCopy when connecting CSV imports. Instead, I've typed "shims surrounding"XmlReader each first-level element should be shown as a row in anIDataReader - really quick.

22
1/13/2009 10:52:22 AM

Popular Answer

The column mappings on both data sources, regardless of the sensitivity of the databases, are case sensitive, as I discovered while moving data from Access to SQLSERVER 2005.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow