| Author |
Topic |
|
ch9862
Yak Posting Veteran
76 Posts |
 Posted - 2007-01-29 : 18:11:17
|
| I hope this is the place for questions about DTS.I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?Is there some sort of tutorial out there that explains use of global variables?Thanks in advance for any and all leads |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-29 : 18:42:30
|
| The execute package task can pass global variables to the child.To get any values back I think you would need to execute it using an activex script.There is a task which can set any values in a connection from variables and if I keep typing I'll remember what it's called.Dynamic properties task.Remember to use disconnected edit to remove the server/database name from the entries then it will take them from the connection. You can also do the same in an activex script by looping through the tasks.seehttp://www.nigelrivett.net/DTS/SetDTSRunTimeValues.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2007-01-30 : 06:52:33
|
| Thanks, that was helpful! However - I'm still missing something.I've defined GV in the "master" package, and I can use dtsrun to set their values at run-time.I've also defined GV in the "worker" packages, and added the Dynamic Properties task to assign their values to connection properties, like user names / server names.But I can't seem to find a way to propagate the values of GV from the "master" package to the "worker" packages. I tried Dynamic Properties task placed in the "master" package, but although I can see the "master" GVs there, I can't see "worker" package's GVs.I also tried using VB script in the same place, but although I can enumerate tasks (including exectuion of "worker" packages) - I don't know how to reference their GVs...Could someone steer me in the right direction?TIA |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-30 : 07:45:27
|
| The execute package task has inner and outer package global variables that you can use to set the values.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2007-01-30 : 08:45:15
|
quote:
Originally posted by nr
The execute package task has inner and outer package global variables that you can use to set the values.
But how?I added one GV to the "worker" package (WorkerServer). I set its value to "not set".In "worker" package I have one Script task, which does: MsgBox DTSGlobalVariables("WorkerServer").ValueI added one GV to the "master" package (MasterServer). For simplicity I set its value to "YESSIR" (instead of having it assigned by dtsrun).I have one Execute Package task in "Master" package, which of course executes "worker" package. In the properties of the task, on "Inner Package GVs" tab I select "WorkerServer", and have it set to "hello" (I tried not leaving it blank - doesn't help). In the "Outer Package GVs" I select "MasterServer" (there's no value to set); EDIT: and I just realized it's not needed.But when I execute the package, I get a message box with "hello" - so it seems like what I set at design time was pushed down. How do I assign a value at run time? Is there some trick I'm missing? Do I need Script task in the "master" package to push the value to the Execute Package task (and how)?Thanks in advance for any and all help... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-30 : 11:41:53
|
| Just tried itThe names have to be the same in both packages.Set the outer variable to the global variable and it will be passed to the child.Apparently the inner and outer both pass to the child but at different times.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2007-01-30 : 12:43:07
|
| Got it - thanks so much!! |
|
|
|
gparrulli
Starting Member
1 Post |
Posted - 2007-02-20 : 19:19:16
|
quote:
Originally posted by ch9862
Got it - thanks so much!!
I too needed to do the same thing. However I also need the sub package to change the GV value and then I need to be able to read that changed value in the master package. Any Ideas? |
|
|
|
praveenkjohn
Starting Member
9 Posts |
Posted - 2007-03-14 : 03:41:43
|
| Please Help me out , i have two DTS packages in SQL Server 7 , which i have to run from the SSIS i almost done everything except i need the global variables that i have declared in the SQL Server 7 at SSIS . Means How to access the variable from SSIS . I tried Execute DTS 2000 package , but dont know how to access the variable from thereRegardsPraveen John+91-9895074288"Frankly, my dear, I don’t give a damn" |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-03-14 : 04:34:05
|
| I suggest you start a new thread for this - you'll have a much better chance of getting a solution that way.Mark |
|
|
|
|
DTS and variables