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

  • dndaughtery

    SSChampion

    Points: 11261

    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

     

    TSQL:

    DECLARE @Variable XML

    DECLARE @VAriableString VARCHAR(MAX)

    SET @Variable =

    (Select top 4 ID

    from [dbo].[Company]

    FOR XML PATH)

    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?

     

  • dndaughtery

    SSChampion

    Points: 11261

    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