Home » SQL Server

Assign guid to variable in control flow

How do I assign a script task generated GUID to an ssis variable and then pass it to sql server via a stored proc? I've only been able to find info on how to do it in a data flow task (derived column)

There doesn't seem to be a GUID type in the Data Type drop down in the Variables tab

I get the following error whether I create the variable as an object or string & setting the data type of the variable to either object,guid or string in the parameter mapping tab of the execute sql task don't do much either

Error: 0xC002F210 at Log Fail, Execute SQL Task: Executing the query "EXEC dbo.spLogGenericExtractControlOnComplete
    ..." failed with the following error: "Conversion failed when converting from a character string to uniqueidentifier.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Log Fail


What magic combination of settings do I have to use to make it work?


2 Answers Found


Answer 1

You can put the guid  value into a String variable.  If you look at the System variables, there are a couple that have GUID in their name.  They are String variable.

Next try mapping  the variable  to the parameter  using the NVARCHAR datatype:  http://www.proteanit.com/b/2008/05/12/ssis-system-variable-executioninstanceguid/.


Answer 2

That's with 2005 though, I'm using 2008. I verified that the ExecutionInstanceGUID that i'm logging in my custom table matches the guid  in the sysssislog(or is it ssissyslog) table


But nevermind!! I got it working.. It was the curly brackets after all


My user variable  is of type  String & called FileExecutionGUID

my custom script  task =

Dts.Variables["FileExecutionGUID"].Value = '{'+System.Guid.NewGuid().ToString().ToUpper()+'}';


my stored  proc has the parameter  defined as uniqueidentifier

the execute  sql task  parameter mapping  has

User::FileExecutionGUID | Input | GUID


It'll probably work  without the upper, but I threw in a breakpoint and compared the System::ExecutionInstanceGUID value to my generated  guid and noticed that mine was missing {} and that it was in lower case



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure