SSIS and GUIDs and Strings and Casting

Long time no post...

So I was working with GUID's and storing and passing them around in SSIS as User Variables and parameters without much luck.

To avoid all of the "TYPE" issues with GUID and strings and GUID types I was storing it from the sproc into SSIS as a User Variable type of STRING.

Well, nothing was working and my down stream spocs were not finding GUID specific result sets as I passed the User Varible to the sprocs as parameters in SSIS.

The issue...eventhough I defined the SSIS User Variable as a type of STRING...the sproc that was returning the value into SSIS was of the type UniqueIdentifyer...and SSIS decided to "get smart" and stored it as a UniqueIdentifying in the User Variable eventhough it was of type String.

To fix this I had to case the return type to a Varchar(50) to fix the issue.

How did I know this was the issue. Well, I put a break point in the SSIS package after I returned the GUID and viewed the User Variable I was storing it in and noticed an extra set of brackets around the variable...so my GUID was stored in the string as

{73BEAE7A-148E-443B-BECD-964D3EC25DCA}

and likewise passed to the down stream sprocs as the same {73BEAE7A-148E-443B-BECD-964D3EC25DCA}...and since its a VARCHAR(50) it was trying to match the sproc parm {73BEAE7A-148E-443B-BECD-964D3EC25DCA} to the value 73BEAE7A-148E-443B-BECD-964D3EC25DCA.

No match...DUH...so by casting the result of the GUID to a Varchar(50) in the spoc that generates my GUID it gets passed into SSIS and the User Variable correctly so that down stream these extra backets are not an issue.

Enjoy
QuiJon