User::Variable not being updated in an SSIS Package

Question

User::Variable not being updated in an SSIS Package

Jim Covington

Old Hand

Points: 348
More actions
October 5, 2010 at 3:58 pm

#143635

I need to get access to a package variable to use in the steps of my ssis package.
I have created the Variables as type String and a scope of the Package.
I have a SQL Task with a "Select 123456 as TRDATE" in the SQL Statement in the step.
In the Result Set section, I have "Result Name" --> TRDATE tied to USER::TRDATE
When I execute the step, no value appears in the Variable WIndow for USER::TRDATE
I must be missing a step somewhare.
Thanks
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Jim Covington (10/5/2010)
I need to get access to a package variable to use in the steps of my ssis package.
I have created the Variables as type String and a scope of the Package.
I have a SQL Task with a "Select 123456 as TRDATE" in the SQL Statement in the step.
In the Result Set section, I have "Result Name" --> TRDATE tied to USER::TRDATE
When I execute the step, no value appears in the Variable WIndow for USER::TRDATE
I must be missing a step somewhare.
Thanks
Jim

I seem to remember needing to use the column position instead of the column name - try using 0 or 1 instead of TRDATE.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

Just because you update a variable when your package executes does not mean that the value of the variable will be persisted in the Variables window afterwards.

Try putting a script task after your Execute SQL to display the value of the variable in a message box - something like this:

Public Sub Main()

MsgBox(Dts.Variables("User::TRDATE").Value)

Dts.TaskResult = ScriptResults.Success

End Sub

(remember to add User:TRDATE to the list of ReadOnlyVariables for the script task)

(remember also that (I think) TRDATE is case-sensitive)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Jim Covington (10/5/2010)
I need to get access to a package variable to use in the steps of my ssis package.
I have created the Variables as type String and a scope of the Package.
I have a SQL Task with a "Select 123456 as TRDATE" in the SQL Statement in the step.
In the Result Set section, I have "Result Name" --> TRDATE tied to USER::TRDATE
When I execute the step, no value appears in the Variable WIndow for USER::TRDATE
I must be missing a step somewhare.
Thanks
Jim

What you're seeing is normal. I have never seen the value of the variable updated after the execution of the package.

Do what Phil said to check that the variable is updated during the execution, but don't expected that value to be retained after the execution is completed.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 4

What you're seeing is normal. I have never seen the value of the variable updated after the execution of the package.

Neither have I, so I assume that's the expected behavior. When I need to know what variables were determined I write them out to a table.