TypeConversionMode property in execute sql task in SSIS

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. ".