SqlBulkInsert and Guid problem

Question

Currently I am importing large sets of data from an ASCII file containing a Guid. A sample row of import data would be:

35313532-3200-0000-0000-000000000000,PRT100,MYCORP ENTERPRISES, ...

The problem is with the first column which is in SQL server mapped as Guid. I get the following error: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.

The column seems ok to me. Anyone knows how to import a string representation of a Guid into a real Guid?

Here's the function in question:

private static void ImportBc(string sInputConnectionString,
                             string sOutputConnectionString,
                             string sInputTable,
                             string sOutputTable,
                             string[,] arrMap,
                             bool bDelete)
{

    Console.WriteLine("Import datoteke {0}......", sInputTable);

    if (bDelete)
    {
        var sqlCnn = new SqlConnection { ConnectionString = sOutputConnectionString };
        sqlCnn.Open();
        //var deleteCommand = new SqlCommand("TRUNCATE TABLE " + sOutputTable) { Connection = sqlCnn };
        var deleteCommand = new SqlCommand("DELETE FROM " + sOutputTable) { Connection = sqlCnn };
        deleteCommand.ExecuteNonQuery();
        sqlCnn.Close();
    }

    string oledbConnectionString = sInputConnectionString;

    var connection = new OleDbConnection(oledbConnectionString);




    var command = new OleDbCommand(sInputTable, connection);
    connection.Open();

    // Create DbDataReader 
    using (DbDataReader dr = command.ExecuteReader())
    {

        // SQL Server Connection String
        string sqlConnectionString = sOutputConnectionString;

        // Bulk Copy to SQL Server
        using (var bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {

            bulkCopy.BatchSize = 10000;
            bulkCopy.NotifyAfter = 10000;
            bulkCopy.SqlRowsCopied += OnSqlRowsCopied;
            bulkCopy.DestinationTableName = sOutputTable;

            /* Zbog timeout expired problema */
            bulkCopy.BulkCopyTimeout = 9000;


            for (int i = 0; i < arrMap.GetLength(0); i++)
            {
                bulkCopy.ColumnMappings.Add(arrMap[i, 0], arrMap[i, 1]);
                Console.WriteLine("{0} {1}", arrMap[i, 0], arrMap[i, 1]);
            }



            bulkCopy.WriteToServer(dr);
        }
    }
}

Could you please show us how you insert the values? – as-cii Mar 21 '11 at 13:09 — as-cii, Mar 21 '11 at 13:09

score 0 · Answer 1 · edited May 23 '17 at 12:09

A few days ago I published an answer to this question:

I was thinking in two things you should do,

a) add the mappings, but you seem to have added them properly:

 // Let's fix tons of mapping issues by
 // Setting the column mapping in SqlBulkCopy instance:
 foreach (DataColumn dataColumn in _dataTable.Columns)
 {
     bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
  }

b) Set the KeepIdentity option:

SqlBulkCopyOptions.KeepIdentity

Looking at your code I guess that your ArrMap does not have all the columns to be mapped, Why don't ypou give a try to my Generic Bulk Insert class? (using it like explained in that answer.

I hope it helps to find out the error source.

Juan

score 0 · Answer 2 · answered Mar 21 '11 at 13:09

0

Have you tried it in this format: '35313532-3200-0000-0000-000000000000',PRT100,MYCORP ENTERPRISES, ... ?

answered Mar 21 '11 at 13:09

Alan

6,501
1
28
24

The same with the apostrophes. – zszep Mar 21 '11 at 15:26

SqlBulkInsert and Guid problem

2 Answers2