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

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 19, 2020 at 2:36 pm

  #3735606

  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: 11290

  More actions

  March 19, 2020 at 2:59 pm

  #3735607

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