Tags: assign, col, contain, database, fixed, hii, importing, maintained, microsoft, mysql, oracle, output, server, sql, ssis, uniqueidentifier, value, variable, wand

SSIS variable that should contain uniqueidentifier

On Database » Microsoft SQL Server

1,840 words with 3 Comments; publish: Wed, 26 Dec 2007 06:52:00 GMT; (25047.85, « »)


I am importing data and wand to assign a fixed value to an output col. the fixed value should be a uniqueidentifier maintained in a variable so that it can easely exchange prior to execution. My problem is that I realized that there is no variable type for uniqueidentifier. When trying to store the guid in a string variable and then trying to cast back using a script component i received a casting exception telling me that a string cannot be converted into a guid.

I dont want to use an object type because then i would have to manually read out the variable a execution start.

Does anyone have experience using varialbes and guids?

Thanks a lot


All Comments

Leave a comment...

    • I find that a Derived Column can convert a string variable to a GUID effectively using the following expression:


      #1; Tue, 04 Sep 2007 02:55:00 GMT
    • I am attempting to convert a string variable containing a GUID to aDT_GUID data type so I can store the resulting value in a table column defined as a uniqueidentifier. I am using an expression similar to the one you suggested ((DT_GUID).sqlserver.todaysummary.com.[User::File_Log_Id]), however, I get the following error message and I'm not sure what I am doing wrong. The user variable File_Log_Id is defined as datatype "string".Thank you for any help you can provide.

      "Error: 0xC020700C at Data Flow Task - Load File Data onto Load Table, Derived Column for Date and other conversions [3929]: The expression "(DT_GUID).sqlserver.todaysummary.com.[User::File_Log_Id]" on "output column "File_Log_ID" (10764)" is not valid."

      #2; Tue, 04 Sep 2007 02:56:00 GMT
    • It appears that casting to DT_GUID in SSIS requires the GUID string to have the curly braces: i.e., if you generated a guid from a script task you'll need to "{" + guid + "}" on it.
      #3; Tue, 04 Sep 2007 02:57:00 GMT