November 18, 2009 at 2:51 pm
Hi all,
I'm new to SSIS, and practially the first thing I want to do is get the highest value from my target table so I can go get the newer records from my source table. It seems like it should be a total snap, but I'm having HUGE problems with getting the stupid number into a variable. (And I haven't even tried to wrestle anything OUT of the variable again yet!)
My base SQL code is simple -- essentially: select max(logid) as MaxLogID from MyTable
My key field is a bigint, so I've set the user variable User::MaxLogID to Int64, because as far as I can determine from these two pages, that's what I need to use:
http://msdn.microsoft.com/en-us/library/ms141036.aspx
http://msdn.microsoft.com/en-us/library/ms345165%28SQL.90%29.aspx
bigint = DT_I8 = System.Int64
So what I've done is this:
- create a variable named MaxLogID with scope of the entire package
- create an Execute SQL Task named "Get last logId" which has a ResultSet of Single row, and the sql statment is select isnull(max(logid),0) as sqlMaxLogID from MyTable. Nothing in Parameter Mapping because there's no input. On the Result Set tab, Result Name of sqlMaxLogID = Variable Name User::MaxLogID. Nothing on Expressions tab.
When I execute the task, it turns red and says:
SSIS package "ETL Log and LRM tables.dtsx" starting.
Error: 0xC002F309 at Get last logId, Execute SQL Task: An error occurred while assigning a value to variable "MaxLogID": "The type of the value being assigned to variable "User::MaxLogID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".
Task failed: Get last logId
SSIS package "ETL Log and LRM tables.dtsx" finished: Success.
Is a bigint NOT the same as Int64?? Am I missing something??
Thanks very much!
November 18, 2009 at 3:15 pm
this is a known bug:
search "SSIS - SQL Server BIGINT doesn't map to INT64"
on https://connect.microsoft.com/default.aspx
workaround is to use STRING
[font="Arial Narrow"]bc[/font]
November 18, 2009 at 3:22 pm
ARGH!
Well that helped, thanks. 🙂 On to the next step -- DOING something with the result!
November 18, 2009 at 3:56 pm
Is a bigint NOT the same as Int64?? Am I missing something??
this is a known bug:
search "SSIS - SQL Server BIGINT doesn't map to INT64"
on https://connect.microsoft.com/default.aspx
workaround is to use STRING
It is not a bug if you are developing in a x86 which is 32bits box because INT64 is a x64 only variable that is you can only use it in a 64bits box. I can understand why developers thinks it is a bug because BigINT is the same in values with INT64 but INT64 being .NET is platform dependent. Here maybe the reason for the confusion so Microsoft needs to correct the docs.
http://msdn.microsoft.com/en-us/library/ms131092.aspx
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply