The TypeConversionMode option is new in SQL Server 2012. This option
allows the Execute SQL Task to convert data types when saving to a variable.
The data types for SSIS variables do not match exactly to the data types in SQL
Server. This mismatch can cause headaches due to needed data conversions. In
SQL Server 2012, that headache is relieved with the new TypeConversionMode
option. Set this mode to Allowed and the Execute SQL Task will convert some
items to match the variable types when necessary.
By default value of this property is Allowed.
Let’s see the below example
Suppose we are executing our sql script task and our script return
string and we want to assign this value in the package int type variable. If we
are setting the property as Allowed then the package automatically converts the
string data type to int.
We are taking a variable with int.
Running the below query
It will return string.
Select Result Set as Single Row.
This applies to
the single-row result set type.
Now executing this task.
Executed successfully.
Now we are setting the property values As ‘None’.
Now running this task.
This task failed.
See the error.
|
[Execute
SQL Task] Error: An error occurred while assigning a value to variable
"var_int": "The type of the value (String) being assigned to
variable "User::var_int" differs from the current variable type
(Int32). Variables may not change type during execution. Variable types are
strict, except for variables of type Object.
".
|
