使用SqlBulkCopy向TLC_PRODUCTS_LOAD导入数据时遇类型转换错误

Key Fixes to Resolve the Issue #

• Verify Column Mapping Alignment: Even after modifying the table's data type, confirm your SqlBulkCopy column mappings explicitly match source and target columns. Implicit mapping can lead to mismatches if column orders differ. Use SqlBulkCopy.ColumnMappings.Add() to define exact pairings, ensuring the source string column maps to the updated target column (whether it's now a string type or a properly compatible numeric type).

• Clean Invalid Source Data: Check for non-numeric values, empty strings, or whitespace in the source column intended for the bigint target. For example:

  • Values like "123xyz" or " " will fail conversion.
  • Validate and clean data before passing it to SqlBulkCopy. Use long.TryParse() to filter invalid entries if using a DataTable as the source:

    foreach (DataRow row in sourceDataTable.Rows)
    {
        string rawValue = row["YourSourceColumn"].ToString().Trim();
        long validBigInt;
        if (!long.TryParse(rawValue, out validBigInt))
        {
            // Handle invalid entry: set to DBNull, skip row, or log error
            row["YourSourceColumn"] = DBNull.Value;
        }
    }
    

• Confirm Schema Changes Were Applied: Ensure your table modification (changing the target column's data type) was saved and committed. Run this query to verify the current schema:

  SELECT DATA_TYPE 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'TLC_PRODUCTS_LOAD' 
    AND COLUMN_NAME = 'YourTargetColumn';
  

• Check Bigint Range Compatibility: If you kept the target column as bigint, confirm all source string values fit within the bigint range (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807). Values outside this range will trigger conversion failures even if they're numeric.

内容的提问来源于stack exchange，提问作者Yolula Sisilana