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.

Advertisements

Entry filed under: Uncategorized.

Summer Reading List match.com open house.

1 Comment Add your own

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

    Hi,
    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.

    -Jamie

    Reply

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Calendar

June 2008
M T W T F S S
« May   Dec »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Most Recent Posts


%d bloggers like this: