Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
 Posted - 2004-11-26 : 06:15:15
|
| I have a DTS package which just truncates a table and then refreshes this table from a central server... Unfortunately I have to do this same process on over 150 different servers... The process is the same for each... Can someone please tell me if and how I can create the pump task to work with a variable for the connection??Thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-11-26 : 11:48:56
|
| OK an update... I can now dynamically get the connection for the truncate no problem, I just can't seem to find out how to do this for a datapump task... All I need to change each time is the destination connection?!?! Any ideas as i'm really stuck on this and can't seem to find anything on the web about doing this (if it can be done at all without writing VB!!) |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-11-27 : 01:51:31
|
| Have you tried the "Dynamic Properties task"Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
nreinholdt
Starting Member
8 Posts |
Posted - 2004-11-27 : 21:59:11
|
| Yes, I believe a "Dynamic Properties task" is the answer as Hemanth Gorijala suggested.When you add it and open its properties, click Add, then expand the "Connections" node and then select the "Microsoft OLE DB Provider for SQL Server" node. That will show you a property called DataSource where you can change the server name (or Catalog to change the catalog within the server). Since the datapump only cares about what data connection it is referencing, I suspect that changing the data connection's Datasource will do the trick for you.Good luck,Nielsnragamuffin |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-11-29 : 06:30:57
|
| Thanks, that seems to work...Any idea how to loop a few steps a certain amount of times?? |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-11-30 : 00:05:08
|
At the end of last step add a ActiveX script task to change the status of first step (which ever step you want to loop to) to pending. See below code for sample...Function Main() Dim pkg Dim stpbegin set pkg = DTSGlobalVariables.Parent set stpbegin = pkg.Steps("DTSStep_DTSExecuteSQLTask_1") 'The trick to looping in DTS is to set the step at the start of the loop ' to an execution status of WAITING stpbegin.ExecutionStatus = DTSStepExecStat_Waiting Main = DTSTaskExecResult_SuccessEnd FunctionHemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-11-30 : 00:07:34
|
quote:
certain amount of times??
I'll leave this part for you to figure out....HINT!!!HINT!!!....heard of global variables.....  Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-11-30 : 04:03:17
|
Thanks...  |
|
|
|
|
|
|
|
|
Dynamically specifying connections??