DTS get servername
-
Hi all,
I was wondering if anybody knew of a property etc. of a DTS package to return the servername where the package is stored? Please don't recommend creating a connection and doing an @@servername as all connections are based on the server that the DTS resides on.
Thank You,
Bryan Coonfield
-
There is a property of the Package Object named CreatorComputerName, which gives the name of the computer the package was created on. But when the package is saved onto another computer it retains the creator computer's name.
Alternatively (A Non-Brainy Method):
The following code can be used to get the server name:
DTSGlobalVariables.Parent.Connections(<name_or_ordinal_number_of_connecton>).DataSource, where <name_or_ordinal_number_of_connecton> is the name of the connection within quotes or the ordinal number of the connection (without quotes). But the obvious hitch here is that you should have a connection which is made to the package's parent Server.
I'll look for a better method and let you know, if I come across one.
-
Bryan
My initial thoughts are that you can't get the information you need without creating some sort of connection. You could use a bit of VBScript to query the computer name via WMI.
What are you trying to achieve here?
--------------------
Colt 45 - the original point and click interface
-
"I was wondering if anybody knew of a property etc. of a DTS package to return the servername where the package is stored? Please don't recommend creating a connection and doing an @@servername as all connections are based on the server that the DTS resides on."
Isn't the server where the DTS is stored the same as where it resides?
I am not exactly sure what you are looking for here.Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
I don't have SQL Server running right here/now, but was trying to deal with the same situation; in short, there is a need for the concept of having a global/system variable that represents the concept of "this DTS package's host SQL Server". There is a system table somewhere named sysservers. It has a row for each server, including linked servers. It appears that the current server always has an ID of 0. But I guess within DTS you'd need to already have a connection pointing at the server so cancel that idea. What I ended up doing was to have a HostSQL global variable which everything was controlled by - server connections, log server, execute package tasks...
-
What I am specifically trying to do is point our connections to the right server depending on whether the DTS script is on our test or production server. For example, if the DTS script is on the server "server1" then I want the Finance connection to point to the finance database on "server1" otherwise I want the connection to point to "server2". Historically we have accomplished this by a parameter sent in to the DTS script from the DTSRUN command line. This maybe the best way to do this but I wanted to see if anyone else had any ideas.
Thanks,
Bryan
-
Naw. If you've already got an accepted ActiveX script task or Dynamic Properties task to set the server properties, that's pretty much the "best practice" for doing it. This is my opinion after lots and lots of research. There are many variations on it, but it always boils down to script/dynamic properties being used to set the connection property.
-
Here's an example to put in an activex task within the package to determine the server name:
Set WshNetwork =CreateObject("WScript.Network")
DTSGLOBALVARIABLES("gvServer").VALUE=WshNetwork.ComputerName
MsgBox ("Computer Name = " & DTSGLOBALVARIABLES("gvServer").VALUE)
set WshNetwork =nothing
-
I tried this but it seems to report the name of the computer running the script rather than the name of the computer where the DTS is hosted.
Thank You,
Bryan
-
I'm not sure what you mean by "running the script" and "hosted". For my example, I had the code in an activex task within a DTS package. If I simply run that DTS package, it returns the name of my PC. However, with some code added (left out before to keep it short) to write out the name to a text file just for example purposes:
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set FS = CreateObject("Scripting.FileSystemObject")
Contents="Computer name: " &WshNetwork.ComputerName&" "& now & vbcrlf
Filename="\\Server\Shared folder\test.txt"
Set OutStream = FS.OpenTextFile(FileName, ForAppending, True)
OutStream.Write Contents
set outStream=nothing
set FS=nothing
I then scheduled the DTS Package, so it could be run as a job on the server. When I run it as the job, it runs on the server instead of my local machine, and writes the name of the server to the file.
So, if by "running the script" and "hosted" you mean running a script like this from the command line where "package name" is "hosted" on the server:
DTSRun /S "Server" /N "Package Name" /W "0" /E
... then you are still running it locally,not on the server, so you will get the local machine name. If that is what you want to do then you probably would need to pass in the server name as a parameter.
-
That's why I said my opinion on getting the server identification was based on "lots and lots" of research. There ain't no other way to do it. The problem is related to the fundamental fact that the DTS package itself is a COM object that just happens to be stored in sysdtspackages on some server. There are some barriers between the two entities that are impossible to cross; try writing T-SQL to print out the task properties of a DTS package without resorting sp_OA procs, for instance.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply