Insert GUID with SqlBulkCopy

Question

I am trying to perform a bulk insert with the SqlBulkCopy class from flatfiles created by the SQL Server Management Import Export Wizard. Files are separated by a comma.

One line in the file might look like this:

{DCAD82A9-32EC-4351-BEDC-2F8291B40AB3}, {ca91e768-072d-4e30-aaf1-bfe32c24008f}, 900001: 1792756,900001: 1792757, basladdning, 2011-04-29 02: 54: 15.380000000, basladdning, 2011laddning, -29 02: 54: 15.380000000, {20A3C50E-8029-41DE-86F1-DDCDB9A78BA5}

The error I get is:

Thrown System.InvalidOperationException event

Message = this String value from the data source cannot be converted to the uniqueidentifier type of the specified target column.

So the problem is converting from string to GUID.

I tried the following:

• Tried different encodings, UTF8 and ANSI
• Deleted {} surrounding GUIDS
• Added '' around GUIDS, with and without {}

Any suggestions? In the flat file where "" is located, there is a column that also has a type of Guid as a data type. The NULL column where the wizard exported it as. '' Could this be a problem?

The code:

 using (var file = new StreamReader(filePath)) using (var csv = new CsvReader(file, false)) using (var bcp = new SqlBulkCopy(CreateConnectionString())) { bcp.DestinationTableName = "table"; bcp.WriteToServer(csv); } 

Table definition:

 CREATE TABLE [beata].[T_FeatureInstance]( [FeatureInstanceId] [uniqueidentifier] NOT NULL, [FeatureInstanceParentId] [uniqueidentifier] NULL, [FeatureTypeAliasId] [uniqueidentifier] NOT NULL, [Uuid] [varchar](1000) NOT NULL, [VersionId] [varchar](50) NOT NULL, [SkapadAv] [varchar](255) NULL, [Skapad] [datetime] NOT NULL, [UppdateradAv] [varchar](255) NOT NULL, [Uppdaterad] [datetime] NOT NULL, [Gs] [uniqueidentifier] NOT NULL, 

Answer 1

The problem is that the empty column in {DCAD82A9-32EC-4351-BEDC-2F8291B40AB3},,{ca91e768-072d-4e30-aaf1-bfe32c24008f} interpreted as '' instead of NULL . I just tried INSERT STATEMENT in my local database, and this error disappeared when I specified NULL instead of '' You can get another error:

 Conversion failed when converting date and/or time from character string. 

The reason for this is that SQL allows only three-digit decimal precision in milliseconds. Thus, you should round '2011-04-29 02:54:15.380000000' to '2011-04-29 02:54:15.380' , and it will work fine.

One way to do this would be as indicated here. Alternatively, you can change the CsvReader code to return DBNULL when this value is string.Empty in the CsvReader.GetValue (int) function. Look here

Answer 2

In your DataTable.Columns.Add specify the data type as the second parameter. This should do the trick. Example:

 DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(Guid)); 

Answer 3

The standard insert in the uniqueidentifier column will look like this (tested and working), so it will answer the question about the formation. Does the column allow null values?

INSERT INTO dbo.TableName ([GUID]) VALUES ('20A3C50E-8029-41DE-86F1-DDCDB9A78BA5')

Answer 4

I think I found the answer for those using the popular free code class called BulkDataReader. I have the same problem, and I tried to post this a little back, but since I asked another question and did not give an answer that the message was deleted. This is normal, I did not understand that this is against the rules.

This time I have an answer, so hopefully this post will stay here. I am going to leave part of the setup of my problem so that it is clearly defined, and no one is confused by why the solution does not work for them. I got the code for the BulkDataReader class from a colleague, and it looks like he could get it from another well-known answer source so that everyone knows how to find BuldDataReader from a search engine.

This issue is configured as follows:

I also tried all kinds of formats for GUIDS in a CSV file, including: N3192E434-F479-4B32-B516-AC658F4B7B89 {3192E434-F479-4B32-B516-AC658F4B7B89} (3192E434-F479-4B32-B516-AC9484F4B4B4F4F4B4B4F4F4F4B4F4F4F4F4B4F4F4F4F4F4F4F4F4F4F4F4FBF 4B32-B516-AC658F4B7B89} "

The real sampling string from my CSV will be: 1, AAPL, 452.2012,2013-01-24 13: 24: 27.000,3192E434-F479-4B32-B516-AC658F4B7B89,3192E434-F479-4B32-B516-AC658F4B7B89

If I delete 2 Guides and import them into a table without these columns, it works fine. With Guides to Unique Identifier columns, I get this error: Message = {"This String value from the data source cannot be converted to enter a unique identifier for the specified target column." }

My control code is pretty simple, where the BulkDataReader is rather cumbersome to walk around, so be prepared if you are trying to debug it.

 using (IDataReader reader = new BulkDataReader(new StreamReader("C:\\test.csv"), false)) { String connectionStr = GetConnString(); SqlConnection connection = new SqlConnection(connectionStr); connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { SqlBulkCopy copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction); copy.DestinationTableName = "TestTable6"; copy.WriteToServer(reader); //ERROR OCCURS HERE: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column transaction.Commit(); } catch (Exception exe) { transaction.Rollback(); } finally { connection.Close(); } } 

Thus, the error does indeed occur in the .NET SqlBulkCopy class. However, this happens immediately after BulkDataReader reads the Guid value. This GetValue method is called from an external code. It means that it was buried in plumbing between StreamReader BulkDataReader and StreamWriting material in the SqlBulkCopy class. There was no need to delve into all my favorite reflection utility. I found that when the BulkDataReader IDataRecord.GetValue (int i) method returns a string that really is a Guid, SqlBulkCopy cannot convert this string to a unique identifier, no matter what format it is in. Probably some kind of obscure and coding format, but I could not find one that would work. However, if I simply return the value as the correct type of System.Guid, then SqlBulkCopy simply converts it to a unique identifier. So a simple solution to what seems like a nightmare serialization issue. Just copy the entire IDataRecord.GetValue (int i) method with this and should work. I tried many CLR data types for SQL, but not all of them, so there may be another where you should play this deserialization game, but this should solve the problem for the most part.

  object IDataRecord.GetValue(int i) { ValidateDataReader(DataReaderValidations.IsInitialized | DataReaderValidations.IsNotClosed); if (((IDataRecord)this).IsDBNull(i)) return DBNull.Value; else { //For some reason the SqlBulkCopy.WriteToServer method will call this GetValue method when reading //the value and it doesn't seem to know how to convert the string value of a Guid to a unique identifier. //However, it does actually know how to convert a System.Guid to a UniqueIdentifer so we can return that if //the value parses to Guid if (IsGuid(this[i])) return Guid.Parse(this[i]); else return this[i]; } } bool IsGuid(object value) { if (value != null) { Guid testGuid = Guid.Empty; if (Guid.TryParse(value.ToString(), out testGuid)) return true; } return false; } 

I hope this helps someone and it’s a pity that I first violated the rules of the blog.