SSIS - SQL Server BIGINT doesn't map to INT64

SSIS - SQL Server BIGINT doesn't map to INT64 by Phil Brammer

Closed

as Fixed

Type:	Bug
ID:	260967
Opened:	3/2/2007 8:51:04 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When setting a variable up in SSIS and setting it to an INT64 data type, returning a bigint value from SQL Server 2005 does not map correctly to this variable type even though it should.

Details (expand)

English

SQL Server 2005 SP1 - Developer Edition

DTS

Windows XP SP2 Professional

US English

Create a table in SQL Server:
CREATE TABLE [dbo].[table_test](
[intColumn] [int] NULL,
[bigintColumn] [bigint] NULL
)

Insert into that table:
insert into [table_test] values (2147483647,9223372036854775807)

In SSIS, on the control flow, add an Execute SQL task that points to that table with the following SQL:
select max(bigintColumn) from table_test

Create a variable, "test", and set it to INT64. Map the Execute SQL Task's result to this variable and execute it. It will fail.

Using the above table and steps, it successfully works against an INT32 variable when selecting the MAX(intColumn).

[Execute SQL Task] Error: An error occurred while assigning a value to variable "test": "The type of the value being assigned to variable "User::test" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

It should map correctly.

0 attachments

Posted by Gogula on 6/15/2010 at 10:51 PM

This issue still seems to be there on SQL Server 2008 (with SP1).

Posted by Microsoft on 4/5/2007 at 11:23 AM

I apologize in the delay for my reply - I didn’t see your response until now.

When an issue reported through Connect turns out to be bug, we log a new defect in our internal system, and the original is closed. Having a closed status doesn’t mean the issue has been fixed, but we do keep track of the history in the new defect report.

We can’t guarantee that a fix will make it into a particular release, but yes, this issue is on the work list for Katmai.

Posted by Phil Brammer on 3/14/2007 at 9:27 PM

So as a result of this being closed (fixed) does this mean the next release will have this corrected? This issue can't die unless it's in the work list for the next release.

Posted by Microsoft on 3/13/2007 at 5:58 PM

Hello,

I’ve looked into this issue and found out some of the history behind the handling of the BIGINT type. At the time we implemented this functionality, there were issues supporting the Int64 data type across platforms. It was decided to return these values are strings instead.

Unfortunately, simply changing the behavior would break existing packages which expect BIGINTs to be returned as Strings. We are currently investigating alternatives, and will consider this fix for the next release.

Thank you.

Posted by Phil Brammer on 3/2/2007 at 8:57 AM

BIGINT fields from SQL Server will map to the SSIS string data type though....