none
Assign guid to variable in control flow

    Question

  • 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?

    Tuesday, October 12, 2010 10:36 PM

Answers

  • 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

    • Marked as answer by jakubk Tuesday, October 12, 2010 11:31 PM
    Tuesday, October 12, 2010 11:31 PM

All replies

  • 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/.


    Russel Loski, MCT
    Tuesday, October 12, 2010 11:07 PM
  • 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

    • Marked as answer by jakubk Tuesday, October 12, 2010 11:31 PM
    Tuesday, October 12, 2010 11:31 PM