title: SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column
body: <p>I'm getting this exception when trying to do an SqlBulkCopy from a DataTable.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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 <strong><em>datatable</em></strong> 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):</p>
  
  <pre><code>//--- create lists to hold the special data type columns
  List&lt;DataColumn&gt; IntColumns = new List&lt;DataColumn&gt;();
  List&lt;DataColumn&gt; DecimalColumns = new List&lt;DataColumn&gt;();
  List&lt;DataColumn&gt; BoolColumns = new List&lt;DataColumn&gt;();
  List&lt;DataColumn&gt; DateColumns = new List&lt;DataColumn&gt;();
  
  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 =&gt; 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 &amp; column having an issue
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>The level 2 exception has this error:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>and the level 3 exception has this error:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Does anyone have any ideas to fix? or any ideas to get more info?</p>