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?

Solution 1

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.

Solution 2

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.