Accessing variables dynamically

Question

Accessing variables dynamically

Pete Bishop

Default port

Points: 1478
More actions
February 6, 2022 at 8:06 pm

#3980985

I have an SSIS package which updates a number of tables and sets a variable upon success. I'd need to review these variables and set an error message accordingly. I'd like to avoid having to change the Script Task every time I add a new table, so is there a way to step through the variables dynamically?
The VariableDispenser.LockForRead() has to be given a valid variable name - so it must know what the valid variables are - but I can't find a way to query it. Variables[] only works if the variables are configured for reading ahead of time - otherwise it's empty - so that doesn't help.
Any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

So you have 2 variables per table, 1 for the table name, one of the success? Then yes, you're going to need to update the script each time. Why not use something like an object variable and store all the details you need in a single dataset variable?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Pete Bishop Default port Points: 1478 More actions · Answer 2

Nice idea. However I use the result of the boolean variable in "Expression and constraint" connections so I'd still need that variable.

It seems strange that there isn't a way to enumerate the variables. I've seen pkg.Variables.GetEnumerator() but I can't find a way to set pkg to the current package, only a newly-created one :-/

Thom A SSC Guru Points: 99130 More actions · Answer 3

It doesn't seem odd at all that you can't iterate through an undefined list of variables to me, if I'm honest. Scalar variables are defined as being individual values; they aren't related to each other explicitly. I feel like you better need to explain what you really want. Like I mentioned, if you want to iterate through a bunch of values, you want an object, not a bunch of unrelated variables.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Pete Bishop Default port Points: 1478 More actions · Answer 4

Dts.Variables[string] is just an array where the string represents the variable's name. Iterating through an array should not be a big deal.

I have variables:

table1_Success, table2_Success, etc.

So all I want is to iterate through the defined variables, if the name matches the pattern, get that table's name from it.

If it can't be done, that's fine.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

If your Script Task can access the .DTSX file corresponding to 'itself', it could read the variable names from it. Not pretty and not recommended, but not impossible either.

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

Pete Bishop wrote:

Any ideas?

Yep, but no one using SSIS seems to like it. Why not just use stored procedures without SSIS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pete Bishop Default port Points: 1478 More actions · Answer 7

Found a workaround. The package has an "overall success" variable, which is just a combination of all the individual success variables. Consequently I'm now stepping through the Expression of this variable and stripping the constituent variables from it that way.