Post reply

Accessing variables dynamically

  • 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?

  • February 7, 2022 at 9:33 am

    #3981063

    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

  • February 7, 2022 at 10:21 am

    #3981079

    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 :-/

  • February 7, 2022 at 10:42 am

    #3981083

    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

  • February 7, 2022 at 10:49 am

    #3981087

    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.

  • February 7, 2022 at 11:21 am

    #3981095

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • February 7, 2022 at 7:29 pm

    #3981183

    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)
    Intro to Tally Tables and Functions

  • February 9, 2022 at 3:23 pm

    #3981531

    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.

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

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