HELP! Cannot pass GUID's through variables?

I have a project that uses GUID's througout and I'm completely stumped.

1) I create a "batch" GUID to batch the records I'm about to process.

2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string works fine

3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable

4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:

a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)

This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).

I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.

I've tried using the type Object and Casting to anything and that doesn't work either.

I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.

All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?

Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.

Thanks!

Maybe a parameterized insert using the SQL Task and a property expression on the query to exchange the value of the GUID ID variable into the insert statement? Have you tried that?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Could you elaborate more on how to implement the property expression in the query? I am doing exqactly as you said using a SQL task and trying to insert into custom logging tables via a sp call. I am using parameter mapping to map system variables to parameters in my sql call, however in my sp I am defining the guids as uniqueidentifiers but ssis system variable guids are strings. What is the easiest way to handle this and convert so it works?

Thanks!

|||

There's some examples here:

Using dynamic SQL in an OLE DB Source component
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Setting Expressions
http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Dynamic modification of SSIS packages
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx

-Jamie