my love hate relationship with SSIS … part 17: curly braces!

As I have already ‘invested’ 3 hours of my life in learning this lesson, I thought I would pass it on to you (or more likely a future version of me that will forget all of this after I sleep again).

Today I wanted to do a simple thing in SSIS (sql server integration services). I wanted to create a derived column so that downstream in the pipeline I could populate the ‘new’ column with a GUID courtesy of a script I found via google. This seamed like such a simple task. Unfortunately I ran into trouble with the derived column transform.

The derived column transform allows you to create a new column of type dt_guid (unique identifyer), but you MUST put something in the expression field. If you don’t provide a value for the expression the transform craps out when you try to close it. I’ll spare you the gory details and just say that the correct expression in this case is a valid guid surrounded by curly brackets AND quotation marks. Example: “{CD7297DF-57BA-4526-B011-639F4AE349BD}” I gotta say … I sure didn’t see that one coming. I tried curly brackets by themselves (brrrrr … try again!), quotations by themselves (brrrrr … try again!), I tried begging, pleading, ignoring errors … nothing worked. That is, nothing except the correct expression. And it only took me 3 hours to riddle that answer out.

My thanks to Jamie Thomson @ SSIS Junkie for the script, and Paul Hunter on the NTSSUG mailing list for the assist.