Recently, we were manipulating a string in an “Execute SQL” task inside a SSIS package, when we ran into the following sequence of errors.
[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object. [Execute SQL Task] Error: An error occurred while assigning a value to variable "MyStringVariable": "The type of the value (DBNull) being assigned to variable "User::MyStringVariable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Error: The type of the value (DBNull) being assigned to variable "User::MyStringVariable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.
The Execute SQL was similar to something that we had done hundreds of times before, and therefore we were stumped by the error. I found the root cause interesting and hence wanted to write about it right away.
The Test Setup
Before we go ahead, allow me to walk through the sample SSIS package which we used to reproduce the issue. As I mentioned, it is a simple SSIS package with a single “Execute SQL Task”.
The “Execute SQL” task simply executes a T-SQL statement that returns a single-row result set and sets a package variable of type “string“.
DECLARE @myVariable VARCHAR(MAX); SET @myVariable = 'SQLTwins'; SELECT @myVariable AS myVariable;
When we execute this SSIS package, it fails with the error referenced above.
The solution was right there in our faces, but we failed to notice it for a while. If we read the error message carefully, we can isolate the following points:
- The data-type of the variable from the Result Set output of the Execute SQL task is different from the data-type of the target user variable
- SSIS detects this as an attempt to change the data-type, which is not allowed because variables types are strict unless defined as an “object”
Based on this, we set about looking at differences between the single-row result set and the SSIS user variable of type “string”. We soon realized that the result set was returning a VARCHAR(MAX).
It appears that the (MAX) was causing problems in the SSIS engine. As soon as we changed it to a fixed-length variable the package worked as expected.
DECLARE @myVariable VARCHAR(8000); SET @myVariable = 'SQLTwins'; SELECT @myVariable AS myVariable;
Hope this little tip helps in your development efforts someday.
Until we meet next time,
Be courteous. Drive responsibly.