I have 2 sql server 2000 instances on the same server. One is servername
(local), and one is servername\test.
I have the same DTS Packag in both. When the package is running I want to
know which server instance I am on, so I can set up connections (put
instancename in DataSource Property) , execute child packages (put
instancename in ServerName property), etc correctly.
I cannot find any property or environment variable or function to return my
current instancename.
I can't use tsql SERVERPROPERTY or @@Server, because I would already need
the servername\instance to open a connection for this. If I use ".", I get
the same as (local).

--
Thanks and Good Luck.
Fergus

The problem is that you only have one set of tools and once they retrieve
the package from SQL Server they no longer have a need for SQL Server. SQL
Server is simply a store for the packages. When you kick off DTSRun then
you pass a location from which to pick up the package. The package has no
idea from whence it came and it really doesn't matter as far as it is
concerned.

One way to do what you want to do is when you are specifying the server from
which to retrieve the package you also send to the package in the DTSRun
statement the name of that server. You can do this by using the /A switch
to assign a value to a global variable.



--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"Fergus" <msdnferg (AT) newsgroups (DOT) nospam> wrote

I disagree that is doesn't matter. The package is going to execute
Transforms, SQL and subordinate packages on connections. It would certainly
be concerned that they all execute on the appropriate server. An Activex
script could ensure this if it could detect the current environment. I have
packages with dozens of connections. When these have been tested on a test
server, this means that dozens of components get manually changed before
moving them to production. Hardly a "best practice".
--
Thanks and Good Luck.
Fergus



"Allan Mitchell" wrote:

Hello Fergus,

That is connection information inside the package. A connection can point
to a server that is not the one from which it is retrieved. There is a disconnect.
What would happen if the package was stored as a structured storage file?
There is then no server whatsoever. There are a number of ways you can
dynamically set your connection information. One of these is the Dynamic
Properties task. You can also roll your own solution.


Does that help.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks, but no, this doesn't help.
The solution I am probably going to implement is a different ini file for
each server. Unfortunately this means a different ini filename is hard-coded
into a dynamic properties task for each server. It's not a huge deal, but it
means the package is not truly dynamic and it's a manual step step "somebody"
has to remember each time it is deployed.
As to where the package is stored, whether in a database or structured file,
it is irrelevant where it comes from. What is relevant is the server it is
running under, either the one that Enterprise Manager is looking at, or the
"S" parameter from DTSRUN.
--
Thanks and Good Luck.
Fergus



"Allan Mitchell" wrote:

Hello Fergus,

You can be more dynmaic that that. If you store your configuration info
in SQL Server then you can configure an entry in cliconfg to alias the settings
server. Each environment would have the server alias configured and you
do then not need to change the package.

The package does not run under a server. The server is only there to store
the package in this instance and even then this is not necessary when you
can use a SSF. You do not need to have SQL Server involved anywhere. Your
package may never look at SQL Server.

When you pick a package up using EM and design it the package still has no
idea about from whence it came.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks. It's looking manageable now.
--
Thanks and Good Luck.
Fergus



"Allan Mitchell" wrote: