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
FOR XML PATH)
Select @VAriableString = Convert(VARCHAR(MAX),@Variable)
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?