SSIS user variable not updating in execute SQL task

Question

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.

Put in a postexecution breakpoint and viewed the variables. There were no changes. — CJS
– CJS, Commented Jul 14, 2016 at 6:40

Joe C · Accepted Answer · 2016-07-14 11:17:12Z

1

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.

answered Jul 14, 2016 at 11:17

Joe C

3,9832 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

graham · Accepted Answer · 2022-02-24 22:37:49Z

0

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.

answered Feb 24, 2022 at 22:37

graham

112 bronze badges

Collectives™ on Stack Overflow

SSIS user variable not updating in execute SQL task

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related