December 30, 2003 at 6:56 am
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
December 30, 2003 at 7:40 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
December 30, 2003 at 8:07 am
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
December 31, 2003 at 12:16 pm
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
December 31, 2003 at 2:15 pm
>>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
January 4, 2004 at 5:03 pm
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
January 8, 2004 at 8:40 am
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