Get back the name of the server in a DTS package

[franck326]

Hello,

Is it possible, into a DTS package, to get back the name of the SQL server on which it is registered (through a property of the DTS package for example) ?

I know it's possible to see the name of a server with the system variable @@servername or this request "Select srvname From master..sysservers" but I don't know how I can allocate the value of these solutions to a variable into an ActiveX script...

Could someone help me ????
Thanks in advance !

Sorry for my english but I'm french

[MAK]

a. In the DTS package create a global variable "ServerName"

b. Create a SQL Task and type the following command

select MyServer=@@servername

Click on parameter
Select output parameters tab
Select "Rowset value"
Assign parameter "MyServer" to the global variable "ServerName"


you can access the global variable "ServerName" using activex script like below



msgbox DTSGlobalVariables("ServerName")
or
myvariable=DTSGlobalVariables("ServerName")

[andi_g69]

What do you actually mean by name of the server, the package is registered on? Do you mean the server where the package is stored? Or the machine the package is executed from? And as a third option, MAK's proposal will give you the name of the server of the underlying connection of the SQL task. Which again can be different from the first two variants.

[franck326]

Thanks a lot for your answer.

But I can't use the MAK's solution because with a SQL task I need to define at least one connection in which the name of the server must be specified.

"Do you mean the server where the package is stored? Or the machine the package is executed from?"
--> It's the same thing in my case in fact.

I'm going to describe my SQL architectural...

Server A (one installation of SQL Server)
Server B (one installation of SQL Server)
Server C (with 3 different installations of SQL Server C, C', C'')

On each of them I have the same databases and DTS packages with some connections on files and databases (and so servers too)...In fact there is one server for the PRODUCTION, one for the PROGRAMMING, one for the BACKUP.....

But I don't want to modify manually all the connections into the DTS packages on each server, it's too long !!!!...Actually all the DTS packages have their connections pointed on the PRODUCTION server.

So the connections have to be dynamic according to the name of which server they are stored into (I guess this sentence is not very english...)
I made an ActiveX script to modify the files' connections but I need to get back the name of the server now and I don't know how it is possible...

In fact my DTS packages must work like this :
1) Get back the name of the local server on which the DTS package is registered on (or executed from that's the same thing in my case).
2) Modify the "datasource property" of the connection to the server in a "Transform Data Task" which has a source connection on a file and a destination connection on a database.

Then I can manage easily the files' connections with my ActiveX script and global variables.

[Rawhide]

One option that is available to you if (and only if) the dts package is only run locally on the Server where it is stored is to use the [local server] name and it will target the server on which it is running.

Otherwise, what I do is to set a global variable to represent the ServerName. In the package, I add a Dynamic Properties task that assigns the global variable value to all connections. Then if I move the DTS package, all I have to change is the global variable.

[franck326]

Thanks Rawhide but how can you specified the [local server] name into a connection ???? Do you talk about the term (local) instead of the name of the server ??? Because this word is not recognized into my database connections in the DTS Packages...The connection doesn't work with that...

Else, is it possible to create one global variable accessible for all the DTS packages of one SQL server ?

Thanks in advance !

[Rawhide]

Yes, that's what I meant. {local}

Are you local on the server or are you trying to do it from a remote client?

[franck326]

I use the Enterprise Manager so I open each "Local Packages" with this tool.

I tried to substitute the name of the server by (local), {local} or [local] ans it's often the same connection error.

SQL server does not exist or access denied

I don't work directly on the server I use my own PC where the client tools of SQL Server 2000 are installed.

[MAK]

right click on the package and clik on disconnected edit , update the connections with (local) and save the package.

Schedule the package to run. If you run using DTS designer or dtsrun you will get the same message.

[franck326]

I have always the same error even with a scheduling.

Anyway the problem is my DTS Packages are executed from an application so with dtsrun.exe.

Aren't there any properties in a DTS Package to know on which server it is registered ??????

Else I think I'm going to use file .INI to store the name of each server but that won't work for the server C which has 3 different instances of SQL Server...

Has someone got a gun or a rope ???

[Rawhide]

Originally posted by franck326
I don't work directly on the server I use my own PC where the client tools of SQL Server 2000 are installed.

Then you are not local on the server, you are accessing it remotely. You can only refer to the Server as (local) if you are in fact local. I thought I was quite clear on that point.

[franck326]

Yes you were clear on that point but even if the DTS package is scheduled with the reference (local) for the connexions that's not working.

But thanks everybody for your answers.
Finally I decided to use INI files to reference the name of the servers.