Using an Execute SQL task to return XML and store it in an SSIS variable string

  • Hey guys, I need to run a query in an execute sql task and return XML converted to Varchar(MAX) and store it in an ssis variable with a datatype of string. This is what I tried



    DECLARE @Variable XML

    DECLARE @VAriableString VARCHAR(MAX)

    SET @Variable =

    (Select top 4 ID

    from [dbo].[Company]


    Select @VAriableString = Convert(VARCHAR(MAX),@Variable)

    Select @VAriableString

    This runs fine in SSMS but when I run the exec SQL task and try to set the result to an ssis variable of type string I get teh following error:

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "Variable": "The type of the value (DBNull) being assigned to variable "User::Variable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.


    Im wanting to store it in a string ssis variable that I can then pass into an OLE DB Source as a varchar(Max) parameter where i will convert it to XML.

    Is there a way to do this in SSIS?


  • I figured it out. I changed Varchar(Max) in my code to Varchar(5000)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply