How do I set the datasouce to dynamically change to the server it resides on?

Datasources can mean different things: (i.e Textfile, Excel, OLEDB Provider
for SQL).

Assuming you mean OLE DB Provider for SQL Server, have you tried just
specifying (local)? You may get into trouble with Authentication problems on
different servers, though.

- Tom


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200508/1

Sorry being vague. Yes, I am trying to connect to an OLEDB SQL Server
Provider. I did try typing (local) in the "Package\Disconnected
Edit\Connection\Datasource". Any other suggestions?

If you mean how do I change the server in a connection. Here is an example
of changing a connection call "MyConnection" to use a server called
"MyServer"


DTSGlobalVariables.Parent.Connections("MyConnectio n").DataSource =
"MyServer"





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

How do set the connection to the current server WITHOUT having to type the
server name in a gloabl variable? I want it to be 100% transportable, meaning
no changes would have to be made if I moved it to another server with a
different server name. I don't even want to have to change a gloabal variable
value.

I am looking for something like @@SERVERNAME which will tell the connection
to use the server it is currenly on. There has to be something out there
that will do this.

Could you use WSH?




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

How would I do that? I am using enterprise manager and am new to DTS. If
you reply please give thorough explainations. Thank you.

In an Active Script task you can do this


dim wsn
set wsn = CREATEOBJECT("Wscript.Network")

DTSGlobalVariables.Parent.Connections("Connection Name").DataSource =
wsn.ComputerName




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

I am using enterprise manager to connect to the SQL server from my computer.
This did change the connection, however it chaged it to the local computer
name I am using not the SQL server name. I think we are very close. I just
need to know how to get the SQL server name not my computer name, by the way
thanks for all of your help thus far.

DTS is a client and will be executed whereever you push the button. if your
box is not the SQL Server and you want to execute the package with no
changes to the package then what I personally do is this

i create an alias on all machines that will run my packages. I do this
using cliconfig. I call the alias DTSSettings. All my SQL Server
connections then use this alias. That way the package never changes but I
simply change the alias.




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