You may get an error when loading a string variable from a varchar(MAX) field via an Execute SQL Task. Here is the complete error I got when loading a variable named SQLStatement:
Error: 0xC001F009 at I_CTHNDL: The type of the value being assigned to variable “User::SQLStatement” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
A solution is to cast the return value of the select statement or, more drastically, modify the field definition in the database.
SELECT CAST(SQLStatement AS varchar(8000)) AS SQLStatement FROM Z_SQLStatements WHERE (SQLStatementRef = 'ExtractHandlings')
8000 is the maximum value allowed in the cast. The SSIS variable happily accepts it.