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.
This is a problem that was already posted on Andrea Benedetti (Italian SQL Server MVP) blog and in mine one..
here the addresses:
SuxStellino (my nick, i'm Alessandro ;-) ):
Both blogs are in italian.. but subject is the same..
We use strings to store bigint format.. bu we know that is a workaround..
So, thanks to you.. this post give us an explanation stored in books online ;-)..
but we are with you.. why MS and SSIS follow this way?
We found problem some times ago.. nearly one year :S
Like our blogs says.. we'll wait for next releases..
1)"ADO.NET doesn't have this problem"
I still have this problem even using ADO.NET in 2005 version.
2)"Although this behavior is documented in the books online entry," There is nothing on that page that documents that BIGINT comes in as a string.
3)"Expect this as a likely change in an upcoming release." It hasn't, so does this mean you will NEVER fix it in version 2005?
Sorry, I think I must have linked the wrong BOL page. There is a comment in the community section from one of our support engineers that describes the work around for this behavior:
I'm not sure why you're seeing this behavior with ADO.NET. Are you sure it's the same scenario? (retrieving BIGINT values using Execute SQL Task and storing them in a variable?)
The behavior was changed/fixed in SQL 2008.
No this isn't fixed in SQL 2008, i'm having the same problem...
Are you sure the conversion you're trying to do is BigInt -> Int64?
This isn't fixed in SQL 2008 R2 either from my experience. The For Each Loop Container using the "Foreach ADO Enumerator" is throwing the mentioned error. Come on folks, someone fix the issue. Denying it definitely doesn't help.
If you're seeing this issue, please open a Connect report with steps on how we can reproduce it. The fix described here covered Execute SQL Task, SQL Server BigInts, and Int64 variables. It should also handle most cases where we are converting BigInt values to Int64 elsewhere in our runtime. Due to differences in data types, it's definitely possible the ADO Enumerator is returning data in a way that we don't expect.
Bigint parameter native sql driver sproc call from SQL task in 2008 r2 mapped to int64 has same problem
I Confirm, that if you use SQL Script Task to store full result into variable.
And then try to loop through it in For Each container, and try to map Int64 variable to bigint result column,
You will get this erro.
tbh this is slopy work, not able to fix a problem in 5 years
The lack of correction of this issue is fully consistent with what we've seen from Microsoft these last few years.
Again - if you have a repro for this issue, please report it on Microsoft Connect - http://connect.microsoft.com/
I've noticed this appears to still be an issue in SQL Server 2012 with latest CU. Execute SQL to Object variable result. Foreach ADO Result retrieving the BIGINT gives an error about converting a Decimal to Int64. It is hard to believe that it is this difficult to handle an Int64 on 64 bit O/X and 64 bit SQL Server.
to me it looks like the LONG data type on the parameter mapping tab in the execute sql task is incorrectly mapped to (DBTYPE_I4)