Can you set "INNER PACKAGE GLOBAL VARIABLES" through an ActiveX Task?
-
I have a simple child package which performs a set of queries based on the Month and Year passed through global variables (a stupid DWH with month/year partitions). I am trying to pass the month and year through the Execute Package Task. Very simple if I hard code it, but I would like to set the values through an ActiveX task because as we go forward, we will be running the query for the previous month to current month. Any help is greatly appreciated!
-->
Have you looked at using the dynamic properties task and assigning values to the execute package task from there?
Yes, the Dynamic Properties Task only has the "InputGlobalVariableNames" Collection which is used to populate a list of Parent Globabl Variables to be passed to the Child Package. I am looking for something like this:
'SET DTS PACKAGE
Set oPkg = DTSGlobalVariables.Parent
' SET PACKAGE TASK
Set oDTS = oPkg.Tasks("TEST Task").CustomTask
'************************************
' HERE IS WHERE I AM LOOKING FOR HELP
'************************************
oDTS.GlobalVariable("GTEST1").Value = 7
oDTS.GlobalVariable("GTEST2").Value = 2003
'************************************
I can't seem to find the "tree" or order to use the Inner Package Global Vaiables (IPGV) through ActiveX. I even exported the the package as a VB Script file and did not find the reference to the IPGV, But BOL says you can modify them (see "ExecutePackageTask Object").
DTS global variables can be passed to the target package. For each such global variable, a GlobalVariable object, which defines the name of the variable and value, is added to the GlobalVariables collection of the ExecutePackageTask object.
-->
For anyone search for this solution it is actually pretty easy. Select the INNER PACKAGE GLOBAL VARIABLES for each variable you wish to modify and do the following:
'SET DTS PACKAGE
Set oPkg = DTSGlobalVariables.Parent
' SET PACKAGE TASK
Set oDTS = oPkg.Tasks("TEST Task").CustomTask
' SET GLOBAL VARIABLE VALUE
Set oDTS.GlobalVariables("GTEST1").Value = "7"
Simply stated (and I am shame...I will do better in the future ya'll), I used "GlobalVariable" rather than "GlobalVariables". One more interesting tid-bit, You can reference the Global variable either by it's name (as shown above) or it ordinal value (oDTS.GlobalVariables(1).Value...1 indexed).
-->
>>One more interesting tid-bit, You can reference the Global variable either by it's name (as shown above) or it ordinal value (oDTS.GlobalVariables(1).Value...1 indexed).<<
Hi jg4 - The reason you can use the ordinal value is that a global variable belongs to the GlobalVariables collection - it's typical that wherever there is a collection you can use either name or ordinal value.
*BUT*, I caution against using ordinal value for two reasons:
1. It's not self-documenting, like names are, so if I use ordinals and go back a month later, I won't understand what I did.
2. I can guarantee that somewhere along the line, I or someone else will make a change that will reshuffle the ordinals and break the code.
I'm glad you found the answer to your problem - welcome to the strange and wonderful world of DTS!
best regards,
SteveR
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply