2

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

enter image description here

Donald Duck
  • 8,409
  • 22
  • 75
  • 99
user1429595
  • 2,635
  • 9
  • 25
  • 30

1 Answers1

3

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

billinkc
  • 59,250
  • 9
  • 102
  • 159