I saved my packages as dta files to allow easy transfer between SQL servers.
The problem I have is that the server name I created the package on is
stored inside the connection. My thought is to use dynamic properties to
change this setting but I am having problems with that.

Is there a way to get the SQL instance name that is currently running the
package? For instance if I run it on my production system I get back SQLPROD
but if I run it on my development box I get SQLDEV. I thought about using an
environment variable, but I actually run two instances (QA and Dev) on the
same system. Then I thought about using an ini file but the full path to the
file is stored in the package and that is slightly different between the
systems.

Any thoughts?

The package runs on the PC/Server on which you press GO.

So setting it to the PRD server is all well and good so long as the job is
scheduled otherwise you will get back the calling PC.

If using 2000 you could read HOST_NAME() into a global variable through an
ExecuteSQL task and then assign that tp the correct property.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jason Callas" <JayCallas (AT) hotmail (DOT) com> wrote

Thanks for responding Alan.

HOST_NAME does not work because I have two instances of SQL on the same box.
One is referenced SERVERNAME and the other is SERVERNAME\instance.

I did find a solution. With 2000, I am able to pass in global variable
values as part of the command line. When I set up the job, I set the entry
to pass in the @@SERVENAME variable. That way, whichever server I am
creating the job on gets the correct value.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Correct you can also do that You could read that in though in the package as
well to a global variable. Either way is good though

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jason Callas" <JayCallas (AT) hotmail (DOT) com> wrote