Howdy all. My goal here is to be able to be able to move a DTS Package from
one box to another and not have to specify the new box name in the DB
connections. I know I can do it in the Global Variables under Package
properties, but that's not all that great either.

Therefore I try to assign the value of @@ServerName to a GV, but it's not
working as expected. I have a GV named gvBoxName, and in the Dynamic
Properties Task I use the Query option and assign the Data Source value(under
the DB connection) to "select @@ServerName". All seems good as the Package
works with success. But then I "save as" to save the Package to another box.
I then exec the Package from the new box but it's still running against the
old box. Is there a way to either make this work or something that will
accomplish the same goal?
--
TIA,
ChrisR

ChrisR wrote:
To run the query "SELECT @@SERVERNAME", you need a SQL connection, so
using that to change the connection, which is required to run the query
in the first place does not make much sense.

Perhaps you could use the name localhost.

Realisitically if you have machines with named instances and such like
you can expect to have to set this from outside the package. I do this
with my packages. They run as scheduled jobs, and the job passes in the
server name through DTSRUN and /A to set a global variable.

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

ChrisR wrote:
@@SERVERNAME is a TSQL function so a connection to the server is
required before it can even work.

You can create a connection that uses a UDL (Data Link) file. Check the
option "Always read properties from UDL file". You then need to ensure
that the UDL is located in the same path on each server.

Depending on how the package is run it may make sense to pass the
server name from the calling code by assigning a global variable.

If you run the package on the server and you think you'll only ever use
a default instance then you can specify localhost as server name. I'd
recommend you avoid doing that in most cases however, because it limits
your options for re-deploying with virtual instances at a later data.

--
David Portas
SQL Server MVP
--

Darren Green wrote:
You could also maintain an alias name, see Client Network Utility, and
code that in your packages. May help, unless you have more than one
instance per machine.

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

Hello.

Is it possible to obtain the Windows server name the DTS package is
running on dynamically from within the package? TSQL will not work for
me (@@SERVERNAME) because it requires a predetemined connection. So
it should be some sort of DTS/ActiveX Script magic.

Is it possible to use Win32 from these ActiveX Task objects?

Thank you in advance!