Fetch Server Name into DTS Package

Question

Fetch Server Name into DTS Package

HookSQLDBA7

SSChampion

Points: 10709
More actions
December 30, 2003 at 6:56 am

#107339

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

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

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

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

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

Greg Larsen SSC-Insane Points: 20966 More actions · Answer 2

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

sumlin Old Hand Points: 353 More actions · Answer 3

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

sql_servant Old Hand Points: 315 More actions · Answer 4

>>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

darren.green SSChasing Mays Points: 641 More actions · Answer 5

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

fromnaija SSCrazy Points: 2876 More actions · Answer 6

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