dcsimg
Results 1 to 12 of 12

Thread: Get back the name of the server in a DTS package

  1. #1
    Join Date
    Apr 2005
    Location
    France
    Posts
    13

    Get back the name of the server in a DTS package

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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")

  3. #3
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    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.

  4. #4
    Join Date
    Apr 2005
    Location
    France
    Posts
    13
    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.

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  6. #6
    Join Date
    Apr 2005
    Location
    France
    Posts
    13
    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 !

  7. #7
    Join Date
    Feb 2003
    Posts
    1,048
    Yes, that's what I meant. {local}

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

  8. #8
    Join Date
    Apr 2005
    Location
    France
    Posts
    13
    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.
    Last edited by franck326; 04-28-2005 at 04:26 AM.

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  10. #10
    Join Date
    Apr 2005
    Location
    France
    Posts
    13
    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 ???

    Last edited by franck326; 04-28-2005 at 07:59 AM.

  11. #11
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  12. #12
    Join Date
    Apr 2005
    Location
    France
    Posts
    13
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •