Dynamic Property, Global var and SQL task Paremeter

[bteamgold]

Hello,
This is my first post to this site. It looks like a great spot for hard to find DTS info.

I have a DTS package that works fine. It uses Global Vars, Dynamic Property, Execute Sql Task Parameter. I saved a copy to do something similar to another table. But I am have trouble with the parameterized query when it runs. The new saved package does not seem to retain the Execute Sql Task parameter input.

The package always runs successful but does not delete the old data because the parameter keeps losing the global var. meaning that the Execute SQL Task I set the parameter (?) to the Global var (gbMonth), run the job ( successful but does not delete), but when I go back to look at the parameter setting, the global var is blanked out again.

I have:

DTS Package Property: created a Global Variable name = gbMonth, type = string, value = 2007

Dynamic Property Task: Des. Property = InputGlobalVariable, Source Type = Global Variable, Source Value = gbMonth.

ActiveX Scrip Task: I set DTSGlobalVariables("gbMonth").Value = <value returned from prompt>

Execute SQL Task Property: Parameter Mapping: Input Global Variables = gbMonth , Parameters = Parameter 1


Input Global Variables = gbMonth is blank after I run the job…

Any thought? Clear as mud...This is the 3rd time I've copied this package to modify. Last time (tool long to remember how), I had to keep playing around setting before it finally retained the value. Not sure the sequence I followed.



Thanks in advance

 

[Sweenster]

Try setting the Global Variable gbMonth back to 2007 and then manually executing each step from the start and then re checking the Global Variable in package properties after each step. This should help you find where the GV is being set blank.

Assuming steps run in the order shown I think it’s likely that your Active X script is doing something odd as this is the step you are actually changing the GV to what you want.
For testing you could display a msg box at the end of the Active X step to show what gbMonth is set to.

 

[bteamgold]

Thanks Sweenster, you got me pointed in the right direction. While executing the single steps, it made me look at the order of steps. The Dynamic Task Property was listed as last in the execution of steps. I connected a workflow between the dynamic task asn activeX script. Now it works fine.


thanks for the help :-D

b-teamgold

 

[bteamgold]

I looked at the original DTS package and noticed the Dynamic Task Property was never linked by a workflow and was listed last in the steps when the package runs.... So I'm not sure linking it to the workflow was the proper solution, but it did seem to help me out here.