DTS Execute Package Task

• Dirk

  SSC Enthusiast

  Points: 150

  More actions

  February 15, 2002 at 2:34 am

  #151664

  Hi,

  I am using a DTS Package to call another DTS Package using the 'Execute Package' Task. I need to pass a value to one of the Child Package's Global Variables. I managed to do all of this. Now I want to automate this process by calling the Child Package multiple times assigning it a different Global Variable each time. According to the HELP one is suppose to use the 'ImputGlobalVariableNames' property of the ExecutePackageTask Object to assign Global variables from the outer (Parent) DTS Package to the target (Child) DTS Package.

  Please assist me on how to assign the values to the Child Package's Global variables.

  Thanks,

  Dirk

• g.firpo

  Old Hand

  Points: 316

  More actions

  February 15, 2002 at 3:15 am

  #423895

  We had resolved a problem of this kind (see the forum "Transform from a select to multi-files").

  We DON'T use the ExecutePackageTask.

  We use an ActivexScript that cycle and call the "child" package, modifying every time his global variable.

  This is the sample of VBScript in the "main" package:

  ------------

  Dim objPackage

  ...

  While (<condition>)

  Set objPackage = CreateObject("DTS.Package")

  ' load the child package

  objPackage.LoadFromSQLServer "SERVER", "user", "pwd",,,,,"DTS_package_CHILD"

  'Set the global variables

  objPackage.GlobalVariables.Item("gv_A").value = "AAAA"

  objPackage.GlobalVariables.Item("gv_B").value = "BBBB"

  'Execute the package

  objPackage.Execute

  objPackage.Uninitialize()

  Set objPackage = Nothing

  .....

  next ...

  Wend

  ------------

  A possible variation to thi code is to execute the LoadFromSQLServer and Uninitialize out of the cycle.

  Ciao

  Gianangelo

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728159

  More actions

  February 15, 2002 at 10:38 am

  #423933

  I too use an Active X script to call another package in one of my scripts. Never liked the Execute Package task.

  Steve Jones

  steve@dkranch.net

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com

• Dirk

  SSC Enthusiast

  Points: 150

  More actions

  February 19, 2002 at 7:10 am

  #424112

  Thanks, the LoadFromSQLServer method works fine. The only problem might be an speed issue, when using the Package Execute task one can run many tasks parallel (asynchronously) and, depending on the DTS Package Properties and Hardware available, it has a huge speed advandtage. The ActiveX Script calling recursively calling the LoadFromSQLServer method, which I assume runs synchronously (one after the other), is quite a bit slower. But maybe that is one of those buy-offs !

  Regards,

  Dirk

• p_a_bala

  SSC Rookie

  Points: 43

  More actions

  May 16, 2002 at 12:56 pm

  #429299

  quote:

  ---

  Hi,

  I am using a DTS Package to call another DTS Package using the 'Execute Package' Task. I need to pass a value to one of the Child Package's Global Variables. I managed to do all of this. Now I want to automate this process by calling the Child Package multiple times assigning it a different Global Variable each time. According to the HELP one is suppose to use the 'ImputGlobalVariableNames' property of the ExecutePackageTask Object to assign Global variables from the outer (Parent) DTS Package to the target (Child) DTS Package.

  Please assist me on how to assign the values to the Child Package's Global variables.

  Thanks,

  Dirk

  ---

• p_a_bala

  SSC Rookie

  Points: 43

  More actions

  May 16, 2002 at 1:03 pm

  #429300

  Hi

  I do something similar to what you do. By cycling and displaying all the global variables in the sub (executed) package, I found that the variables passed from the executing package are appended to the golbal variables collection. Hence code like this will help you do what you want:

  'Get Global variables passed by

  'Driver package into Local Variables.

  'These are appended to the end of

  'the global variables collection by DTS.

  DTSGlobalVariables("LJobName") DTSGlobalVariables(DTSGlobalVariables.count -1)

  DTSGlobalVariables("LStepName") = DTSGlobalVariables(DTSGlobalVariables.count)

  LjobName and LstepName are the local variables corresponding to the one passed.

  Hi,

  I am using a DTS Package to call another DTS Package using the 'Execute Package' Task. I need to pass a value to one of the Child Package's Global Variables. I managed to do all of this. Now I want to automate this process by calling the Child Package multiple times assigning it a different Global Variable each time. According to the HELP one is suppose to use the 'ImputGlobalVariableNames' property of the ExecutePackageTask Object to assign Global variables from the outer (Parent) DTS Package to the target (Child) DTS Package.

  Please assist me on how to assign the values to the Child Package's Global variables.

  Thanks,

  Dirk

  [/quote]