The type of the value being assigned to variable differs from the current variable type.

August 12th, 2014
by Kristof

This post addresses the following error message.

The type of the value being assigned to variable differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

In my case, the error was because I tried to assign a varchar(max) to a string type variable in SSIS. It turns out that varchar(max) is not assignable to a string type object. The best option is to forcefully convert the value to a varchar(8000) or nvarchar(4000) before assigning it.

I previously stated that a string variable in SSIS can only contain 4000 characters, but that is not correct. A string in SSIS has an unlimited length, but the maximum length of an evaluated expression is 4000 characters, so concatenating over 4000 characters using an expression will result in an error. However, when assigning a value to a string from a resultset, the length is actually unlimited and you can assign over 4000 characters to a string using this method. This also means that you can work around the SSIS expression limitation by using a SQL box to concatenate two strings using a select ? + ?, for example.

Posted in SSIS | Comments (1)

One Response to “The type of the value being assigned to variable differs from the current variable type.”

  1. Dave Vickery Says:

    I was scratching my head for a good hour trying to work this out. Thank you!

    Dave.

Leave a Reply

SEO Powered by Platinum SEO from Techblissonline