I have five identical databases on five different servers. I have a
warehouse server that I was to merge data from all five databases
into. I want to create one DTS package and be able to send a
parameter to the package identifying the server to get the data from.
I want to avoid maintaning five packages if possible. Everything in
the DTS package is the same for all five databases except the source
server name. Can I send a variable to use for the connection to the
package at run time?

Thanks,

Walter
waltmallon (AT) yahoo (DOT) com

Hello Walter

You sure can. One way would be to store your connection info in an ini file and assign the parameters from the ini file to the connection task using the dynamic properties task

hth


----- Walter Mallon wrote: ----

I have five identical databases on five different servers. I have
warehouse server that I was to merge data from all five database
into. I want to create one DTS package and be able to send
parameter to the package identifying the server to get the data from.
I want to avoid maintaning five packages if possible. Everything i
the DTS package is the same for all five databases except the sourc
server name. Can I send a variable to use for the connection to th
package at run time

Thanks

Walte
waltmallon (AT) yahoo (DOT) co

Just to expand on this, you would need a consistent file path on all servers
for the INI file, so it can always be found. You then read the server in
from the INI file and assign it to connections as needed.

An alternative would be to use Data Link files (UDLs). So again a consistent
file path is required, but this file will include all connection
information, and are used directly by a Connection in DTS, see Data Link
Connections.
A final alternative since you just require the server would be to pass it in
at execution time. I consistently use DTSRUN and the /A parameter to pass in
a server name to all packages when scheduled since you may not always be
able to set a consistent file path, such when crossing multiple
active-active clusters.


--
Darren Green
http://www.sqldts.com



"K" <anonymous (AT) discussions (DOT) microsoft.com> wrote

using the dynamic properties task.