Home » SQL Server

SSIS variable that should contain uniqueidentifier


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



9 Answers Found


Answer 1

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



Answer 2

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)@[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)@[User::File_Log_Id]" on "output column "File_Log_ID" (10764)" is not valid."


Answer 3

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.

Answer 4


Two curly braces was all that stood between hours wasted on experiments and success. Thanks for posting, man!


Answer 5

Kris,   another thanks for the posting - I too spent hours.   the curlys!!!!!!!!!!   I will remember that one!


Answer 6

Thanks for the post - after several hours of frustration trying to import GUIDs from XLS, my pain is finally over.


Answer 7

Can u display the whole string  to cast  includingDT_GUID added to the "{" +guid+"}"


Answer 8

(DT_GUID)("{"+[FieldName]+"}")  - This goes in the DerivedColum expression WITHOUT an = at the beginning

Answer 9

Yes it worked , Thank you so much wolfen351.


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure