how to store sql query result in a variable and messegeBox

Question

enter image description here

I have a simple sql query in my Execute sql task in ssis package, SELECT MAX(binindex) FROM dbo.myTable

I need to store this maximum index into a variable and then pass it to Script Task and display it, I already declared a package variable, the package compiles, however it shows -1 every time, I don't know what I'm doing wrong, any help will be appreciated!

enter image description here

public void Main(){
    //TODO: Add your code here
    Dts.TaskResult = (int)ScriptResults.Success;
    MessageBox.Show(Dts.Variables["User::BININDEX"].Value.ToString());
}

enter image description here

What is the data type of the column binindex in dbo.MyTable? What is the data type of the SSIS variable @[User::BININDEX]? — billinkc
– billinkc, Commented Jun 11, 2012 at 21:04
Do you get the same result when you run the query in SSMS (I assume not else you wouldn't be asking the question) — billinkc
– billinkc, Commented Jun 11, 2012 at 21:07
Sorry, I meant the General Tab. Not enough tea this afternoon. Curious about the connection manager type and the resultset property — billinkc
– billinkc, Commented Jun 11, 2012 at 21:10

billinkc · Accepted Answer · 2012-06-11 22:07:27Z

The good news, is that you are doing everything correctly as far as I can see. I recreated your package and I get the expected value from my query.

Basic package

I can also induce your situation - the correct value is returned from my query but my package produces an "incorrect result."

negative one

The problem, I hope, is that you have two BININDEX variables defined at different scopes. My original assumption was the Package scoped one contained a value of -1 and you had a variable scoped to the "Execute SQL Task" with the same name. The default behaviour is a variable is created scoped to the object that currently has focus. This changes in the 2012 release of SQL Server by the way.

As your picture shows a design-time value of 123 for the package scoped variable, the possibility also exists that you have a variable defined on the Script Task with the same name of BININDEX. The local variable would override the globally scoped variable

variables window

Click on your Script Task and hopefully you'll see a BININDEX defined there like the above. Otherwise, I think the problem is somewhere in your package, you have conflicting BININDEX variables. You can try slogging through the Package Explorer looking for an instance where you have two variables with the same name listed.

package explorer

I need to leave but if none of that is the case, add a PostExecute breakpoint on the Execute SQL Task and look at your Locals window (not Variables as that only reflects Design-time values). Expand Variables and you should be able to see the value of BININDEX. Is it correct there?

enter image description here

Collectives™ on Stack Overflow

how to store sql query result in a variable and messegeBox

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related