Execute SQL TaSk

I am trying to run a Direct Input SQL query to SELECT MAX value of ColA. This query when I run on Query Window runs fine - means it is NOT a NULL. I get a max value.

When I run this query on a SSIS package outputing to a variable - I get the error. -

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

I need this output to be used for the subsequent steps which follows this.

I am using Value Type as Double. This was the default when I migrated this DTS package from SQL 2000. I use the proper Single Row for the Result Set and for outputing the Result Set, I use the correct Variable Name.

Help will be greatly appreciated...

Thanks.

SAM.

Obvious first question, what is the type of ColA?

I had some trouble with populating Double variabls recently, I couldn't load it from a field of type decimal. I had to cast the value as a float first.

-Jamie

|||

ColA is my ROWKEYID - it is bigint datatype.

-Sam

|||

Try making the variable type "Int32" or "Int64".

-Jamie

|||

Kept the value type as Double and when I tried to make this as

SELECT CAST(MAX(ROWKEYID) as FLOAT) from Table on SSIS package, it runs fine, successfully, but the value does not change. I mean, when I run on this Query window -the value is different.

But when I try -

SELECT Max(ROWKEYID) FROM Table and making the variable type as Int32 or Int64, the step fails.

-Sam

|||After debugging the task, I found that the value returned by the MAX function on a bigint column is of type String. Thats why your task failed when you used variables of type Int32, Int64 or Double. I see the same behavior when MAX function is called on a decimal column. However, MAX function on a int column returns Int32 type and float column returns a Double type.