how to assign query results tovariable(int32) in execute SQL task

Question

Post reply

how to assign query results tovariable(int32) in execute SQL task

samita_sawant

Mr or Mrs. 500

Points: 597
More actions
April 8, 2008 at 11:34 pm

#120922

hello,
i m working on a SSIS package in which i m returning query results & trying to assign it to a variable with int32 datatype.
database is oracle & m using the connection microsoft oledb connection for oracle.
in sqltask my query is
Select max(record) from target
here i hv taken resultset as single row type & assigning values to rowname 0 to var1
max(record) is of numeric type.
but when i run the task its giving the error that
[Execute SQL Task] Error: An error occurred while assigning a value to variable "va1": "Unsupported data type on result set binding 0.".
please help me m sruggling alot for the issue.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

sunitkrishna SSCrazy Points: 2233 More actions · Answer 1

you declared the int variable?

---
Thinking is the hardest work there is, which is the probable reason so few engage in it.

Sunitha
😎

samita_sawant Mr or Mrs. 500 Points: 597 More actions · Answer 2

samita_sawant

Mr or Mrs. 500

Points: 597

April 9, 2008 at 12:06 am

#800171

yeah i have declare the variable as int32

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You said you defined the variable as an Int4 and the output of max(record) is of numeric type.

Either CAST your query output or use a (if I remember correctly) Double variable type.

I would recommend using CAST.

samita_sawant Mr or Mrs. 500 Points: 597 More actions · Answer 4

I hv use the cast like below:

Select cast(max(record)as number(12,1)) from target

here i declare the variable to double.thn only its giving the same error.:(

sunitkrishna SSCrazy Points: 2233 More actions · Answer 5

are you using a stored proc?

Are you trying to return the integer value from the query?

In SQl sever we do like this:-

declare @Var1 as int

set @Var1=(select max(field_name) from table_name)

set @RetVal=@Var1

where @RetVal is the output parameter specified in the parameter list of the procedure.

You may even avoid teh extra variable,@Var1.

instead use:

set @RetVal=(select max(field_name) from table_name)

I am not well versed in Oracle.

try to use the equivalent in oracle and let me know

---
Thinking is the hardest work there is, which is the probable reason so few engage in it.

Sunitha
😎

samita_sawant Mr or Mrs. 500 Points: 597 More actions · Answer 6

hello thnx for the reply..............

I hv tried ur solution But the thing is there mismaching in the datatypes of oracle & SSIS so m stuck up.

m not able to map

oracle is not able to recognise the '@' symbol so there is difficulty in passing the variables throgh Execute SQL Task.

So i hv decided to execute the SP through Script Task.Kindly guide if u hv any idea about scripting

pankaj-426531 Ten Centuries Points: 1123 More actions · Answer 7

you shud rather use the execute sql task for this.

Did you try Int16 for var1?

george.ku SSC Rookie Points: 43 More actions · Answer 8

Did you get your issue resolved? I am having the same problem.

samita_sawant Mr or Mrs. 500 Points: 597 More actions · Answer 9

hi the problem 4 is solved can say coz wt m doing is m assigning the query result to string variable1.

Then m taking OLE DB Source & mode of accessing the data through varible2.

setting the evaluate expression property of that variable2 as true.

Then in expression window of that variable giving the query as expression & using the variable1 in that query.

now m getting the source data.

any query revert back to me.

all the best.

Turtle2008 SSC-Addicted Points: 414 More actions · Answer 10

Hi,

Can you please explain it in a more understandable way? I'm a slow learner. I'm having a same kind of problem. I'm using "Execute SQL Task" for the query "SELECT Count(*) FROM Table A". On the resultset properties, my result name is 0 and mapped to my package variable "Count" of int32 type. Task run successfully but could not write the output to the variable "Count".

As per grasshopper's post, I created one more variable "Count1" and on the expression added the line @[User::Count] > 199? @[User::Count] : 199. My row count is greater than 199, but still the value of "Count1" variable has not changed.

Thanks in Advance.

meenal.vrishni SSC Journeyman Points: 84 More actions · Answer 11

Hi,

I am facing the same issue.Not able to assign the return value (number datatype) from an oracle function to the package variable (int32 datatype) in execute sql task.I am using Oledb connection type.

Please help!

Jim1234 SSC Eights! Points: 889 More actions · Answer 12

Hi Samita,

I'm facing the similar issue. I have a user variable @Rowcount of type Int32. In ExecuteSQL task my query is Select count(*) from table1 which is pointing to oracle and assigning the count to @Rowcount in resultant set.

Also this ExecuteSQL task is inside a ForLoop which is checking the @Rowcount==0.

This method works fine when I'm pointing to a MS SQL server, but it fails when pointing to oracle database with the following error

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

".

Please suggest what should be the data type I should set for the @Rowcount variable.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

Jim,

I might point out this is a nearly 3yo thread..

I might suggest that it would be a good idea to start a new thread..

CEWII

sam 55243 SSCrazy Points: 2697 More actions · Answer 14

Hi,

Just declare the variable as 'String' type and then assign the value from ur Query to it.