TypeConversionMode property in Execute SQL Task in SSIS

 This property control whether the execute SQL task attempt to perform data type conversion at run time.

By default, the value of this property is allowed.

    

This property has two values

§  None (Do not convert data type during the run time)

§  Allowed  (Convert data type during the run time)

Let’s see the use of it.

Suppose our the query is returning the string value likes ‘100’ and we are trying to store ‘100’ to the integer value in this case either we need to typecast our query the result set or we need to set the TypeConversionMode property Allowed.

When we set Allowed then it will convert and assign the value to the variable. When we set none for such case it will throw an error.

See the example:

Here we are declaring the variable

  

Result as Int.

Now in Execute SQL Task we are writing the script which returns the String value.

  

Setting the  ResultSet as a Single row

 

Setting the TypeConversionMode as Allowed.

  

Now mapping the result set. 

Now taking the script task to see the value.

Now running this package.

 

See the result.

 

No we are setting the TypeConversionMode property as None

  

Running the package.

Package get failed. 

See the error.

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Result": "The type of the value (String) being assigned to variable "User::Result" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

 If we want to set this property as none in this case we need to typecast the SQL result set.  

Now running this package.

      

Now package executed successfully.