SqlBulkCopy with ObjectReader - Failed to convert parameter value from a String to a Int32

fastmember sqlbulkcopy

Question

I'm doing an import from an XML-based file using SqlBulkCopy (.NET) and ObjectReader (FastMember). The appropriate column mappings have been added.

I sometimes see the message "Failed to convert parameter value from a String to an Int32."

I'd want to learn how to: 1. Track down the problematic table column. 2. Get the ObjectReader's "current"

code example

     using (ObjectReader reader = genericReader.GetReader())
                {
                    try
                    {
                        sbc.WriteToServer(reader); //sbc is SqlBulkCopy instance
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();                           
                    }
                }

Does the "ex" include any further information than the mistake:
System.InvalidOperationException : The given value of type String from the data source cannot be converted to type int of the specified target column.

1
1
6/8/2017 11:26:46 AM

Popular Answer

Simple Response

Simple no is the response. Among the causes.NET's SqlBulkCopy is so quick is because nothing it does is logged. There is no direct way to get further details from the.NET's SqlBulkCopy exception. Having said that, Charles Catriel has written an essay on it and provided a potential solution, which you may read in its entirety on here.

Even though this approach could provide you the solution you need, I advise using it exclusively for debugging purposes since it might have some performance implications if used regularly throughout your work.

Why Opt for a Workaround?

The lack of logging definitely speeds things up, but when you are pumping hundreds of thousands of rows and suddenly have a failure on one of them because of a constraint, you're stuck. All the SqlException will tell you is that something went wrong with a given constraint (you'll get the constraint's name at least), but that's about it. You're then stuck having to go back to your source, run separate SELECT statements on it (or do manual searches), and find the culprit rows on your own.

On top of that, it can be a very long and iterative process if you've got data with several potential failures in it because SqlBulkCopy will stop as soon as the first failure is hit. Once you correct that one, you need to rerun the load to find the second error, etc.

advantages:

  1. reports every potential mistake that SqlBulkCopy might run into.

  2. Reports all offending data rows along with any resulting exceptions.

  3. No changes are committed since the whole process is carried out in a transaction that is rolled back at the conclusion.

disadvantages:

  1. It can take a few minutes for exceptionally big volumes of data.

  2. The errors are not returned as part of the exception that your SqlBulkCopy.WriteToServer() process raises since this solution is reactive. Instead, this helper function is used to attempt to collect all potential problems and the information associated with them when the exception is thrown. As a result, executing your process will take longer than just doing the bulk copy in the event of an error.

  3. The same DataReader object from the unsuccessful SqlBulkCopy cannot be used again since readers are forward-only fire hoses that cannot be reset. You must design a new reader of the same kind (e.g. re-issue the original SqlCommand, recreate the reader based on the same DataTable, etc).

GetBulkCopyFailedData Method

private void TestMethod()
{
   // new code
   SqlConnection connection = null;
   SqlBulkCopy bulkCopy = null;

   DataTable dataTable = new DataTable();
   // load some sample data into the DataTable
   IDataReader reader = dataTable.CreateDataReader();

   try 
   {
      connection = new SqlConnection("connection string goes here ...");
      connection.Open();
      bulkCopy = new SqlBulkCopy(connection); 
      bulkCopy.DestinationTableName = "Destination table name";
      bulkCopy.WriteToServer(reader);
   }
   catch (Exception exception)
   {
      // loop through all inner exceptions to see if any relate to a constraint failure
      bool dataExceptionFound = false;
      Exception tmpException = exception;
      while (tmpException != null)
      {
         if (tmpException is SqlException
            && tmpException.Message.Contains("constraint"))
         {
            dataExceptionFound = true;
            break;
         }
         tmpException = tmpException.InnerException;
      }

      if (dataExceptionFound)
      {
         // call the helper method to document the errors and invalid data
         string errorMessage = GetBulkCopyFailedData(
            connection.ConnectionString,
            bulkCopy.DestinationTableName,
            dataTable.CreateDataReader());
         throw new Exception(errorMessage, exception);
      }
   }
   finally
   {
      if (connection != null && connection.State == ConnectionState.Open)
      {
         connection.Close();
      }
   }
}

GetBulkCopyFailedData() then opens a new connection to the database, creates a transaction, and begins bulk copying the data one row at a time. It does so by reading through the supplied DataReader and copying each row into an empty DataTable. The DataTable is then bulk copied into the destination database, and any exceptions resulting from this are caught, documented (along with the DataRow that caused it), and the cycle then repeats itself with the next row. At the end of the DataReader we rollback the transaction and return the complete error message. Fixing the problems in the data source should now be a breeze.

The method called GetBulkCopyFailedData

/// <summary>
/// Build an error message with the failed records and their related exceptions.
/// </summary>
/// <param name="connectionString">Connection string to the destination database</param>
/// <param name="tableName">Table name into which the data will be bulk copied.</param>
/// <param name="dataReader">DataReader to bulk copy</param>
/// <returns>Error message with failed constraints and invalid data rows.</returns>
public static string GetBulkCopyFailedData(
   string connectionString,
   string tableName,
   IDataReader dataReader)
{
   StringBuilder errorMessage = new StringBuilder("Bulk copy failures:" + Environment.NewLine);
   SqlConnection connection = null;
   SqlTransaction transaction = null;
   SqlBulkCopy bulkCopy = null;
   DataTable tmpDataTable = new DataTable();

   try
   { 
      connection = new SqlConnection(connectionString); 
      connection.Open();
      transaction = connection.BeginTransaction();
      bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
      bulkCopy.DestinationTableName = tableName;

      // create a datatable with the layout of the data.
      DataTable dataSchema = dataReader.GetSchemaTable();
      foreach (DataRow row in dataSchema.Rows)
      {
         tmpDataTable.Columns.Add(new DataColumn(
            row["ColumnName"].ToString(), 
            (Type)row["DataType"]));
      }

      // create an object array to hold the data being transferred into tmpDataTable 
      //in the loop below.
      object[] values = new object[dataReader.FieldCount];

      // loop through the source data
      while (dataReader.Read())
      {
         // clear the temp DataTable from which the single-record bulk copy will be done
         tmpDataTable.Rows.Clear();

         // get the data for the current source row
         dataReader.GetValues(values);

         // load the values into the temp DataTable
         tmpDataTable.LoadDataRow(values, true);

         // perform the bulk copy of the one row
         try
         {
            bulkCopy.WriteToServer(tmpDataTable);
         }
         catch (Exception ex)
         {
            // an exception was raised with the bulk copy of the current row. 
            // The row that caused the current exception is the only one in the temp 
            // DataTable, so document it and add it to the error message.
            DataRow faultyDataRow = tmpDataTable.Rows[0];
            errorMessage.AppendFormat("Error: {0}{1}", ex.Message, Environment.NewLine);
            errorMessage.AppendFormat("Row data: {0}", Environment.NewLine);
            foreach (DataColumn column in tmpDataTable.Columns)
            {
               errorMessage.AppendFormat(
                  "\tColumn {0} - [{1}]{2}", 
                  column.ColumnName, 
                  faultyDataRow[column.ColumnName].ToString(), 
                  Environment.NewLine);
            }
         }
      }
   }
   catch (Exception ex) 
   { 
      throw new Exception(
         "Unable to document SqlBulkCopy errors. See inner exceptions for details.", 
         ex); 
   }
   finally
   {
      if (transaction != null)
      {
         transaction.Rollback();
      }
      if (connection.State != ConnectionState.Closed)
      {
         connection.Close();
      }
   }
   return errorMessage.ToString();
1
8/4/2017 2:49:18 PM


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