How to increment a user variable in on success and on failure execute sql task in BIDS?

Question

I have a simple package in which I'm executing select statement in Execute SQL Task, I want on success of this task my variable @cnt=0 should increase @cnt by one like @cnt=@cnt+1 and in script task i should be able to show it and on failure it should be like @cnt=@cnt-1.

What should I try in precedence constraint, I tried using this '@CNT==@CNT+1' but it does not increase the value of @cnt on success.

my Expression is

Appreciate any help on this.

Answer 1

You could use the Expression Task to set the value of the variable, with precedence constraint of success, and a separate task to reduce it with constraint of failure. The expression in the precedence constraint itself is to evaluate whether something should happen, not to set the value for a variable.