CODEWITHSUNDEEP
ssisuser-variables
CJS
CJS

Reputation: 1

SSIS user variable not updating in execute SQL task

I know this should be really simple, but I cannot get it to work. I am using an execute SQL Task in Control flow with the intention of setting 2 user_variables, vRunID and vRelease. I have a very simple select statement where I am getting a single row, 2 columns:

SELECT Id, release
FROM [Test].[Run] 
where id = (select max(id) FROM [Test].[Run])

in the result set tab, I have tried mapping using "Result Name"s of both the columns (Id and Release) and 0,1 to the "Variable Name"s of User::vRunID and User::vRelease. I cannot get the variable values to change at all. If I hardcode the variable values, everything runs fine, but I would like it to be based off of the select statement so we can just run the package and not have to hard code the values (which is why we use SSIS in the first place) Is there something simple I'm missing? Do I need to set something in the parameters? Watching countless online videos it looks like I have everything set up as I should, but it will not load the values.

Upvotes: 0

Views: 2467

Answers (2)

graham
graham

Reputation: 11

Check the EvaluateAsExpression property of the variable. I had the same issue, but once I changed this property to False, the variable's value would update.

Upvotes: 0

Joe C
Joe C

Reputation: 3993

I think your problem is that the variables window is not updated during execution. I have found two items that confused me about variables in the past. First, the variables current value during execution is found by looking at the watch or locals windows, under the debug menu. Second was that I had to use 'start' instead of execute task / container for this to work. So if I wanted to debug the third container in my package I could either run the whole thing, or if possible, disable the first two containers so that I could 'debug' the third container. Hope this helps.

Upvotes: 1

Related Questions