Post reply

DTS Global Variable problem

  • May 24, 2004 at 3:53 am

    #60106

    I have a DTS set up which checks the status of a process and sends an alert to a user if a certain condition is met.

    Each run checks the condition against a global variable, and when complete, updates the global variable with the new value.

    For example:

    GV1 was preset to "SUCCESS", after very first run, set to the status at the time "FAIL".  DTS package saved.

    DTS runs.

    Current Process status is "SUCCESS"

    If Process status is not equal to GV1 - alert user.

    Update GV1 with current status - and so on.

     

    The problem being that GV1 doesn't retain the latest setting.  According to BOL, a global variable will retain the setting at the last execution:

    A global variable created during design time retains the value it had when the package finished execution, if the package is saved. For example, suppose you create the global variable, "city," and set it to the value of "Boston." During package execution, an ActiveX script changes the value of "Boston" to "Philadelphia." The next time you execute the package, the global variable will contain "Philadelphia," not "Boston." This is useful if you want to query the value of a package global variable after execution.

    Have I completely misunderstood this?
     
    Can anyone shed any light on how to retain global variables for each future run?
     
    Incidentally, this is the command I am using to update the variable:
      ' Set Control variable

      DTSGlobalVariables("PrevRun").Value = DTSGlobalVariables("JobName").Value

    Thanks

  • May 24, 2004 at 12:16 pm

    #507541

    BOL isn't very clear about this, but I believe it means that the package has to be saved during execution after the global variable is reset.  I've never done it, but I think you'd use an ActiveX task and the package object model.

     

    Greg

    Greg

  • May 25, 2004 at 5:18 am

    #507615

    , thanks Greg.

    Not quite sure how to go about that!

    I'll give it a go, but in the meantime, if anyone has any examples, they would be gratefully received.

     

    Thanks

     

  • May 25, 2004 at 8:22 am

    #507667

    Alternatively, you could write the value to a table and then query the table during DTS execution for the value.  You would also be able to set the value at the end of execution based on the results obtained.

    Just a thought. 

  • May 25, 2004 at 8:30 am

    #507671

    Thanks Bellis

    That is what I ended up doing!

    I was just trying to keep everything tidy and not have to have an extra table holding this one value.  It certainly was the easiest option though!

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

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