Data Conversion Issue in SSIS package - Text to GUID

Question

I am developing a SSIS package that will open an Excel spreadsheet and import the data into a database table in SQL Server 2008. When I try to convert the Excel column data type: Unicode String [DT_WSTR] to a unique identifier data type: unique identifier [DT_GUID], I get the following error:

"Invalid character value for cast specification"

What do I need to do to resolve the conversion error?

Are you trying to change the data mappings on "Excel source" or is this failing from a Data Conversion transformation? — billinkc, Sep 10 '12 at 17:14
@billinkc - It is failing from a Data Conversion transformation — Michael Kniskern, Sep 10 '12 at 20:07
Are you converting in the sense of your source being the string and your target being the guid, or are you actually editing the input (or output) columns in the Data Conversion task? — Ann L., Sep 10 '12 at 22:23
Show us one example, one string which you try to convert. Basically your string format is wrong, because string must be formatted before converting to GUID, not all strings can be converted right away. [Example1](http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server), [Example2](http://jesschadwick.blogspot.com/2007/11/safe-handling-of-uniqueidentifier-in.html) — Justin, Sep 11 '12 at 06:56

score 8 · Accepted Answer · answered Sep 13 '12 at 17:02

8

I used a Derived Column Transformation Editor and to wrap the excel column value in squrly brackets {} in order for the SSIS package to properly convert the Unicode String into a GUID.

answered Sep 13 '12 at 17:02

Michael Kniskern

24,792
68
164
231

To clarify, it worked by using the SSIS expression `(DT_GUID)("{" + [ColumnName] + "}")` – Olov May 03 '23 at 12:30

score 3 · Answer 2 · edited Mar 13 '20 at 16:54

I want to mention that this will not work with SSIS 2012 in Visual Studio. If you try to do this you get an error on the derived column transformation task. I've tried both of these:

(DT_GUID)[ColumnName]

(DT_GUID)("{" + [ColumnName] + "}")

Both of these will fail.

However, if you simply set to ignore those errors instead of fail. It will work fine. Really spend way too much time trying to get this to work.

Data Conversion Issue in SSIS package - Text to GUID

2 Answers2

Linked