Why can’t I store my BIGINT result in an Int64 variable?
The native providers (OLEDB, ODBC, ADO – ADO.NET doesn't have this problem) in the Execute SQL Task return the BIGINT type as a String, and not an Int64 as you'd expect. Attempting to store the result in an Int64 variable gives you an error along the lines of:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "xxxx": "The type of the value being assigned to variable "xxxx" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object."
Although this behavior is documented in the books online entry, we don't explain why it was done this way. Being somewhat new to the SSIS development team, I had to do some digging to find out for myself. It turns out that at the time this was implemented, there wasn't cross platform support for the 8-byte integer type (VT_I8/VT_UI8) in VARIANTs -- specifically, on Windows 2000. Now that supporting Win2k is no longer an issue (for Katmai), we're free to change the behavior (while maintaining backwards compatibility for packages that are expecting the value as a string, of course).
Expect this as a likely change in an upcoming release.
