Determine Location Within DTS Package
-
Is it possible to determine, WITHIN the DTS package, the SQL Server it is running on? I.e. the SQLServerRepository server name? We want to dynamically set our first sql connection to this value. NO, we do not want to use an INI file to do this. INI works in most cases, but is unacceptable in our situation. If we can let the package figure out where it is sitting, our package will then be completely portable (other connections/variables/etc are set based upon values in a config table queried in a dynamic dts step following the setting of the sql server connection).
We've considered using the HOSTNAME environment variable, but HOSTNAME and the instance name aren't always the same. Also, what HOSTNAME would show up in a cluster failover situation?
Thanks for your help!
-
Why don't you use "(local)" instead of servername? I think it will work for you.
-
(local) won't work if multiple instances are installed and you want to connect to other than the default instance...
-
Have you tried @@Servername variable?
-
You can't use @@servername or run any other query. Running a query requires you connect to a server. You have to know what server you are connecting to. That's the whole point of this exercise.
-
Can you set up a global variable in the package and then use a dynamic property task to set the connection value to the value of the global variable? If you're executing the package externally (e.g. with DTSRun) then this might be a good thing to try as you're not putting anything in the package itself that specifies the server, and you can pass in the server info when you execute the package.
-
If only one of these DTS packages is running at any given time and that package can access all your SQL Servers (and assuming you don't have too many) and the package is run by a job then you can have it check each server to find out which one has the job running that executes the DTS package and then you will have the instance it is running on.
I know those are a lot of conditions, its simply a thought.
I am researching ActiveX scripts to see if there is something that will identify the instance name. So far I got the DTS package name, versionid, and other things but no instance name.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
OK, here's an ActiveX script (suitable for use in a DTS package) that uses DMO to get the name of the server, into a package global variable (and shows a message box with the name).
Function Main()
Dim sqlObj
Dim nlOobj
Set SqlObj = CreateObject("SQLDMO.SQLServer2")
Set nlObj = sqlObj.ListInstalledInstances
DTSGlobalVariables("test").Value = nlObj.Item(1)
MsgBox(DTSGlobalVariables("test").Value)
Set nlObj = Nothing
Set SqlObj = Nothing
Main = DTSTaskExecResult_Success
End Function
Caveats here are that this displayed "(local)" for my instance, not the actual name of the server/instance. Plus, if there are multiple instances, it'll probably return a list, not a single server name--in this case, I know there's only one, so I got Item(1). But you can check the "Count" property of the "nlObj" variable (it's a "NameList" DMO object) to see if it contains more than one server, which I assume would mean there are multiple instances. Don't know what you'd do with the info in that case, though, and don't know if this is useful or not.
-
Perhaps you can use what dmbaker sent along with this code which gives you the PackageID. That equates to the ID column in sysdtspackages:
Function Main()
Dim ObjectPackage
Set ObjectPackage = DTSGlobalVariables.Parent
MsgBox ObjectPackage.PackageID
DTSGlobalvariables("gvPackageID").value = ObjectPackage.PackageID
Main = DTSTaskExecResult_Success
End Function
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I've been watching this one for a while and I'm still wondering if a DTS package actually runs within a SQL server at all. Surely as DTS can be redistributed without SQL and packages are run from the command line then there may not actually be a server instance to find. I can see that in this instance the package has been stored in SQL but surely once it's running it's just in memory.
-
Here you have a nice solution, works fine!
Function Main()Set WshNetwork = WScript.CreateObject("WScript.Network")
MsgBox("Domain = " & WshNetwork.UserDomain)
MsgBox("Computer Name = " & WshNetworkComputerName)
Main = DTSTaskExecResult_Success
End Function
/Henrik -
I was looking for some different information when I ran accross this thread. I think that what you want is:
SELECT SERVERPROPERTY('SERVERNAME')
as this will provide server or server\instance information for you.
-
I think I just saw the same thing you did, ajrg, and didn't see your post until I clicked reply.
For more info on a way to implement this: http://www.sqlservercentral.com/columnists/tdavid/moreportabledtspackages.asp
-
Most of the methods suggested here will not work against a named instance. The suggestion of running "SELECT SERVERPROPERTY('SERVERNAME')" won't work because you need to have a server connection to run the SELECT statement against.
The only time using (local) or the dot notation works effectivly is when you only have a single default instance of SQL Server.
The best method I've come across is to pass the connection parameters in as global variables from the DTSRUN command line. This removes the need for extra items like INI files, UDL files, Alias settings, etc...
--------------------
Colt 45 - the original point and click interface
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply