Home » SQL ServerRSS

SSIS variable that should contain uniqueidentifier

Hi

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

Alex

 

9 Answers Found

 

Answer 1

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

(DT_GUID)@[User::MyVariable]

 

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

Kris,

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   |   Follow us on Twitter