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.