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

June 16, 2008 at 6:22 pm 1 comment

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.


Entry filed under: Uncategorized.

Summer Reading List open house.

1 Comment Add your own

  • 1. Jamie Thomson  |  June 16, 2008 at 6:40 pm

    You could have saved yourself a lot of trouble. You can create the new column in the same script component in which you use that code that I provided. No need for the derived column.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


June 2008
« May   Dec »

Most Recent Posts

%d bloggers like this: