Inserting GUIDs with SqlBulkCopy

Question

I'm trying to do a bulk insert with the SqlBulkCopy class from flatfiles created by the SQL Server Management Import Export Wizard. The files are comma separated.

One row in the file can 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,2011-04-29 02:54:15.380000000,{20A3C50E-8029-41DE-86F1-DDCDB9A78BA5}

The error I get is:

System.InvalidOperationException was unhandled
Message=The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.

So the problem is the converting from string to GUID.

I have tried the following:

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

Any suggestions? In the flat file where the '' is, is a column that also has Guid as datatype. The column is NULL where the wizard exported it as ''. Can that be the 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

In your DataTable.Columns.Add include the data type as a second parameter. That should do the trick. Example:

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