Hi all,

I am running into an issue here. I am creating DTS packages that
manipulate data. I start on a dev server go to qa, then uat and
finally production. Currently each time I promote the Package to a
different environment I must go in and manually change the sql server
connection. I would like as the first step of the package to
determine which server the DTS is currently saved on and set the
connection with this via a global variable. I can't seem to find a
property for this. Keep in mind that I may run this DTS from a laptop
and need the name of the server and not my laptop. I have also tried
to use a WScript.Shell method that was only returning my laptop name.

Any hope would be greatly appreciated.

Thanks,

Jason

You can use the Dynamic Properties task to accomplish this.

The DP task can read from a source and change the properties in the package
for you.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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" <jjrzut (AT) gene (DOT) com> wrote

My question is similar, so I thought I'd piggy-back on this thread.

I am running a DTS package that creates a file on another server. To
do that, I am using a specific security ID and password to connect to
the other server. I notice that you cannot specify a user ID and
password directly when you choose text file as a source or
destination, but I see that I CAN set user ID and password values
using a Dynamic Properties Task. I set the user ID and password
parameters for that task to global variables (ID and Password) that I
set appropriately. But it still does not work, I am getting Access
Denied. Is there another place in the package where I need to specify
user ID and password to access a directory on another server?

Please let me know!

Thanks!
Kim

Kim,
Accessing a file or directory on another server is based
upon network authentication and that's not something you
specify in a connection to a data source. They are really
two different things. So...whoever is going to be executing
the package will need the appropriate permissions based upon
their network login. You would also want to use UNC paths
and not mapped drives when accessing the folder on the other
server. And then you would need to keep in mind that the
package will execute based upon the security context of
whoever is executing the package and from the location where
you are executing the package. If you run the package from
your PC, it executes under your security context and from
your PC. If you are going to be scheduling the package, it's
executed from the server by the SQL Agent service account
(if the job is owned by a sysadmin).
The whole concept of who executes a package, the security
needed and where it executes is a source of much confusion
so I hope that makes some sense.

-Sue

On 6 Apr 2004 13:21:16 -0700,
kimberly.kordet (AT) nationalcity (DOT) com (Kim) wrote:

Thanks Sue, that was very helpful! Guess I can stop searching for a
place in DTS to put my service ID and password!

Thanks again,
Kim