SSIS Convert string to a guid when importing and saving data

Question

I was trying to convert a unicode string from an Excel File into a uniqueidentifier. There are already answers online that seem pretty straight forward:

I've seen this post: Data Conversion Issue in SSIS package - Text to GUID

and this post: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ce5b4d2-913a-40f1-9797-105783181f5e/ssis-variable-that-should-contain-uniqueidentifier

But this didn't work in SSIS 2012 in Visual Studio 2012 when importing data to SQL Server 2012, I would get the typical conversion error when running it.

In the Excel File the Guid (as a string) was stored in this format:

00bce79b-6c7b-427f-9711-17c19475f6e4

No curly braces or quotes.

Solution

To get this to work, still type in the derived column expression as:

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

But you need to configure the error output and change the columns where you are doing this conversion to Ignore Failure instead of Fail Component.

If you do this and run it, it WILL work and properly convert and import despite what the debugger tells you.

Comments

[David Wilson]

For me, it worked without needing to ignore any errors.

[ravioli]

Thank you, this saved me.