SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column

I'm getting this exception when trying to do an SqlBulkCopy from a DataTable.

Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column.
Target Site: System.Object ConvertValue(System.Object, System.Data.SqlClient._SqlMetaData, Boolean, Boolean ByRef, Boolean ByRef)

I understand what the error is saying, but how I can I get more information, such as the row/field this is happening on? The datatable is populated by a 3rd party and can contain up to 200 columns and up to 10k rows. The columns that are returned depend on the request sent to the 3rd party. All of the datatable columns are of string type. The columns in my database are not all varchar, therefore, prior to executing the insert, I format the datatable values using the following code (non important code removed):

//--- create lists to hold the special data type columns
List<DataColumn> IntColumns = new List<DataColumn>();
List<DataColumn> DecimalColumns = new List<DataColumn>();
List<DataColumn> BoolColumns = new List<DataColumn>();
List<DataColumn> DateColumns = new List<DataColumn>();

foreach (DataColumn Column in dtData.Columns)
{
    //--- find the field map that tells the system where to put this piece of data from the 3rd party
    FieldMap ColumnMap = AllFieldMaps.Find(a => a.SourceFieldID.ToLower() == Column.ColumnName.ToLower());

    //--- get the datatype for this field in our system
    Type FieldDataType = Nullable.GetUnderlyingType(DestinationType.Property(ColumnMap.DestinationFieldName).PropertyType);

    //--- find the field data type and add to respective list
    switch (Type.GetTypeCode(FieldDataType))
    {
        case TypeCode.Int16:
        case TypeCode.Int32:
        case TypeCode.Int64: { IntColumns.Add(Column); break; }
        case TypeCode.Boolean: { BoolColumns.Add(Column); break; }
        case TypeCode.Double:
        case TypeCode.Decimal: { DecimalColumns.Add(Column); break; }
        case TypeCode.DateTime: { DateColumns.Add(Column); break; }
    }

    //--- add the mapping for the column on the BulkCopy object
    BulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(Column.ColumnName, ColumnMap.DestinationFieldName));
}

//--- loop through all rows and convert the values to data types that match our database's data type for that field
foreach (DataRow dr in dtData.Rows)
{
    //--- convert int values
    foreach (DataColumn IntCol in IntColumns)
        dr[IntCol] = Helpers.CleanNum(dr[IntCol].ToString());

    //--- convert decimal values
    foreach (DataColumn DecCol in DecimalColumns)
        dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());

    //--- convert bool values
    foreach (DataColumn BoolCol in BoolColumns)
        dr[BoolCol] = Helpers.ConvertStringToBool(dr[BoolCol].ToString());

    //--- convert date values
    foreach (DataColumn DateCol in DateColumns)
        dr[DateCol] = dr[DateCol].ToString().Replace("T", " ");
}

try
{
    //--- do bulk insert
    BulkCopy.WriteToServer(dtData);
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();

    //--- handles error
    //--- this is where I need to find the row & column having an issue
}

This code should format all values for their destination fields. In the case of this error, the decimal, the function that cleans that up will remove any character that is not 0-9 or . (decimal point). This field that is throwing the error would be nullable in the database.

The level 2 exception has this error:

Error Message: Failed to convert parameter value from a String to a Decimal.
Target Site: System.Object CoerceValue(System.Object, System.Data.SqlClient.MetaType, Boolean ByRef, Boolean ByRef, Boolean)

and the level 3 exception has this error:

Error Message: Input string was not in a correct format
Target Site: Void StringToNumber(System.String, System.Globalization.NumberStyles, NumberBuffer ByRef, System.Globalization.NumberFormatInfo, Boolean)

Does anyone have any ideas to fix? or any ideas to get more info?

Solution

@Corey - It just simply strips out all invalid characters. However, your comment made me think of the answer.

The problem was that many of the fields in my database are nullable. When using SqlBulkCopy, an empty string is not inserted as a null value. So in the case of my fields that are not varchar (bit, int, decimal, datetime, etc) it was trying to insert an empty string, which obviously is not valid for that data type.

The solution was to modify my loop where I validate the values to this (repeated for each datatype that is not string)

//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
{
     if(string.IsNullOrEmpty(dr[DecCol].ToString()))
          dr[DecCol] = null; //--- this had to be set to null, not empty
     else
          dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
}

After making the adjustments above, everything inserts without issues.