Error: 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

If you get the above error and you have no idea why (i.e you haven't changed the data type of a variable) then it is likely due to a data type inference issue with SSIS. You might think that SQL is returning a value of one type when in fact it isn't its returning something else.

This occurs with a number of functions in SQL, the key is that you think your SQL is returning an integer when in fact its returning a decimal or a data type that SQL Server Integration services maps to a string.

Two fiunctions I've encountered this are row_number and scope_identity.

The solution is to explicitly cast your columns in your query you return to SQL Server Integration services i.e. cast(scope_identity() as int) or cast(row_number() over (order by somecolumn) as int)

If you encounter this you can check [BOL] to see what data type is returned by the function.

The issue is documented on the SSIS team blog Why can’t I store my BIGINT result in an Int64 .... This is the same issue.


Published 19 January 2010 19:05 by simonsabin

Comments

20 January 2010 15:56 by SqlServerKudos

# Error: 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

Kudos for a great Sql Server article - Trackback from SqlServerKudos