SSIS hates varchar(max) Error: 0xC002F309

  While attempting to populate a 
  SSIS string variable with the result 
  set from a stored procedure that 
  returns a varchar(max) value, 
  you will get this error:

Error: 0xC002F309 at GenerateVariableXML, Execute SQL Task: An error occurred while assigning a value to variable "xml_clob": "The type of the value being assigned to variable "User::xml_clob" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object

-----------------------------------------------------------------------------

Result syntax from stored procedure:

SET @ReturnXML=@v_xml

SELECT CAST(@ReturnXML AS Varchar(max)) as ReturnXML

-----------------------------------------------------------------------------

PROBLEM: Who knows? Some speculate that the max length of the string variable is 4000 however I have disproved this by dumping 15000 characters into a string variable. I think it has more to do with the fact that Varchar(max)) uses the normal data pages until the content actually fills 8k of data. When overflow happens, data is stored as old TEXT, IMAGE and a pointer is replacing the old content. SSIS must have an issue operating with that possibility.

-----------------------------------------------------------------------------

SOLUTION: Change your casting data type to Varchar(8000):

SET @ReturnXML=@v_xml

SELECT CAST(@ReturnXML AS Varchar(8000)) as ReturnXML

-----------------------------------------------------------------------------

MORE: You can add a Script Task (C#)to your Control Flow after loading the variable to display the value of the String Variable like this:

        public void Main()

        {

            DialogResult button =MessageBox.Show((string)Dts.Variables["User::xml_clob"].Value, (string)"xml_clob",MessageBoxButtons.OK);

            Dts.TaskResult = (int)ScriptResults.Success;

        }