DTS Execute Package Task

Question

Post reply

DTS Execute Package Task

Dirk

SSC Veteran

Points: 200
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

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

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

g.firpo Old Hand Points: 366 More actions · Answer 1

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: 736255 More actions · Answer 2

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

Dirk SSC Veteran Points: 200 More actions · Answer 3

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 · Answer 4

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 · Answer 5

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]