Fetch Server Name into DTS Package

  • How may I dynamically fetch the name of the server into a DTS Package? Is there a servername variable that I could fetch? Also, may I select this servername variable as the Server in the Connection Properties Dialog?

    Edited by - kevinsql7 on 12/30/2003 08:02:52 AM

  • I found it easiest to just declare a variable and set it to the server name (ie. glvServer, string, SQL01).

    Keep in mind that when you move the DTS package to a different server or instance that you will then need to modify the variable.

    The Reference in an Active-X control would then be "VarServer = DTSGlobalVariables("glvServer").value"

    Matt,

    Peace Out!

    Regards,
    Matt

  • If you go with the global variable you can set it when using the DTSRUN option /A. This might allow you to use the package on multiple servers without changing it.

    Gregory A. Larsen, DBA

    Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/

    Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • You can do it via vbscript. Here's a snippet of code that you can work from:

    
    
    Dim objNet, sServerName, sUserName, sDomainName

    Set objNet = CreateObject("WScript.Network")

    With objNet
    sServerName = .ComputerName
    sUserName = .UserName
    sDomainName = .UserDomain
    End With

    DTSGlobalVariables("glvServer").value = sServerName

    Set objNet = Nothing

    David

  • >>You can do it via vbscript...<<

    Keep in mind, though, that if you have a named instance, or multiple instances, or a clustered server, that the name of the NT Server doesn't equal the name of the SQL Server.

    These are unusual cases, but painful if you run into them unaware.

    Chris Hofland


    Chris Hofland

  • Could you please define Server Name? As sql servant has already pointed out the name of the local machine is not always the name of the SQL Server instance, since we now have instances, but also remember Dts is a set of client side components, so do you want the client machine name, the server machine name, or one or more of the many SQL Server instance names that you may be connected to. Dts packages can be stored in a Sql Server table, but they are not Sql Server objects so during package execution the package does not know if you loaded it from the local Sql Server or a file on your desktop.

     

    Darren Green
    SQLDTS.com   |   SQLIS.com   |   Konesans Ltd

  • A more dynamic approach is to use Dynamic Properties Task reading the server name from an INI file and setting it to a global variable.  You could then have the package connect to any SQL Server merely by changing the INI file.  Alternative as suggested you could use the /A parameter of DTSRUN.

     

     


    Joseph

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply