Post reply

See Global Variables from Calling package

  • September 19, 2005 at 7:28 am

    #187602

    Hi,

    I wan't to be able to call a standalone DTS package from several other packages, however I also want to be able to access variables set within the called package from the calling package.  Is there any way of doing this ?

    Thanks

     

     

  • September 20, 2005 at 6:07 am

    #591256

    Yes this can be done several different ways...

    Here is how you can do this via Active-X scripting:

      Const DTSSQLStgFlag_UseTrustedConnection = 256            

      

      Dim objPackage

      Set objPackage = CreateObject("DTS.Package")

      objPackage.LoadFromSQLServer "(Local)","","",DTSSQLStgFlag_UseTrustedConnection,"","","","Child Package Name"

      

      '*** Set Child Package Global Variables

      objPackage.GlobalVariables("gvVar1") = "this is var1"

      objPackage.GlobalVariables("gvVar2") = "this is var2"

      '*** Execute the Child Package 

      Call objPackage.Execute()

      '*** Read the Global Variables from Child

      MsgBox "Child Global Variable gvVar1: " & objPackage.GlobalVariables("gvVar1").Value

      MsgBox "Child Global Variable gvVar2: " & objPackage.GlobalVariables("gvVar2").Value

      '*** Garbage Collection

      Set objPackage = Nothing

     

    -Mike Gercevich

  • September 20, 2005 at 9:26 am

    #591380

    I had no idea that you could call a DTS package like that, thanks Mike

  • September 29, 2005 at 4:30 am

    #593970

    Mike,

    Is there any way that you can trap the success of failure of the objPackage.execute.  It doesn't appear to return anything

    Thanks

    Simon

     

  • September 29, 2005 at 5:54 am

    #593983

    The Package and extended Package2 objects Execute method do not return a Result.  If you know that exery Step in your Package should execute with a Step Result of Success, you would need to check each individual Step Objects Result:

    Dim oSteps

    Dim oStep

    Dim bPackageResult

    bPackageResult = False

    Set oSteps = objPackage.Steps

    Set oStep = CreateObject("DTS.Step")

    For Each oStep IN oSteps

      If oStep.ExecutionResult = 0 Then

          bPackageResult = True

      End If

    Next

    Set oSteps = Nothing

    Set oStep = Nothing

    If bPackageResult Then

       MsgBox "Package was successful."

    Else

       MsgBox "Package Failed!"

    End IF

    -Mike Gercevich

  • September 29, 2005 at 12:18 pm

    #594142

    Thanks for that Mike,

    Essentially you could also programmatically ignore the flow stored in the DTS job with this and decide your own flow at run-time I guess.

  • September 29, 2005 at 5:15 pm

    #594223

    Simon,

    Correct.  Each Step in the Steps collection has an Execute method.  You could monitor the ExecutionStatus property and on completion, determine Success or Error state. (If you really really had that need!)

    HTH,

    -Mike Gercevich

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

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