SSIS Variables Do Not Like Varchar(MAX)
y2008m03d07
Tom VdP
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.
Your post was really helpful.Thanks
Thanks for the tip, but my string really is larger than 8,000 characters, which is why I was using varchar(max). Is there any way to get SSIS to handle this? Is the SSIS Exec SQL Task return String type really limited to 8,000 characters?