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?
Indeed, the SSIS Exec SQL Task is too limited. We ran into this limitation too and worked around it by using a Script Task to execute the SQL. Strings in the Script Tasks are not limited to that silly 8000 characters limit.
I return it as varchar(max) and assign to Object variable.
How to then in VB script task get the string out of this object?
Convertsion of _ComObject to String is not permitted
Help
Valentin, are you sure you need an Object variable ? Anyway, try the .ToString() method, that should do it, I would say.
.ToString() doesn’t work. See my post below for a solution.
See my post below for solution.
@Tom VdP: You have to assign it to an Object variable because the string variable will not accept varchar(max) result sets. I found that the .ToString() method doesn’t work after you save the result set to an SSIS object variable. All that does is return the data type, not the value of the string itself.
@Tom VdP: Rather than use a script task to execute the SQL, which is more extensive to code and less modular, I found a simpler approach.
To All,
Basically, you have to use a ForEach Loop to “shred” the object variable saved from the SQL Task. See here for details on how to do that (credit Jamie Thompson – SSIS guru): http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx
Once you have your varchar(max) results saved to the Object variable, you loop through it, which in some cases will be only once if all you needed was a single string value in the first place. In the loop’s variable mappings, you map the results from the object variable to an SSIS string variable, and VIOLA! The string value from your varchar(max) result set is now saved to an SSIS string variable.
The strange part is that I do not know why the ForEach loop properly stores the string value of the varchar(max) result set to an SSIS string variable, but you cannot do it in a script task. But hey, this works, so I aint complaining. BTW, this workaround is confirmed in SSIS 2008, although I cannot imagine it would not work in 2005.
Actually, I am trying to do just that with a 600 char varchar (from a varchar(1000) field) string, and it is failing.
The SQL-string is loading well in the Object Variable, together with some ID fields. When I display the Object contents the string is shown complete and correct. The next step is to loop through the rows in the Object variable. There, each row is read and fed to separate variables. This step is not working, the foreach loop can’t seem to read the long string properly, and when I MsgBox the variable it just shows a blank.
Jamies blog posts are no longer available either (I saved hyperlinks instead of downloading the pages…).
Any ideas ?