July 12, 2006 at 5:36 pm
Hi,
I need to identify what server i am on when executing a dts job. Or rather I need to be able to grab this within the package and use it to determine what happens within the flow. So probably store it once identified within a gv, so i can reference it throughout the pkg.
Can seem to find out how to do this!! Any steer would be appreciated.
July 14, 2006 at 12:01 pm
SQL Function "select @@SERVERNAME" will return the current server.
July 14, 2006 at 12:33 pm
Well, not exactly if executed from a DTS package. It will return the name of the server of the connection object your Execute SQL task uses, which may or may not be the same server the DTS package itself resides on.
I don't think there is a way to return what server a DTS package is being executed from because in order to execute it, whether via the Enterprise manager, a DTSRun line in a sql job, or stored procedure, you have to know what server it's on.
Why do you need to know this? Does the source file and/or destination object change based on the server the package is executed from?
July 17, 2006 at 3:15 pm
Hi Eric,
Did some more reading and it does look like you cant do it, as you suggested.
Basicially got dev, test, prod setup and wanted to drop a generic activeX into all dts pkgs that will make sure some processes dont happen. Also the distination changes and also I want to be able to valid the connection, thats its against the right instance.
Cheers
July 17, 2006 at 3:30 pm
He's what I do with DTS packages to deal with development/testing/production scenarios:
I have a .ini file in the same location (c:\ini\sqlvalues.ini) on all SQL servers and developer's machines. The ini file will contain various section names along with a parameter called ServerName. This server name is different on each .ini file
---------------------------------------------------------
ex on development server:
[DTSInfo]
ServerName=DevelopmentSQL
ex. on production server:
[DTSInfo]
ServerName=ProductionServer
----------------------------------------------------------
The paths of all source and destination files used in my DTS packages are stored in a table called ApplicationPaths. This table has 4 fields (SectionName, KeyName, ServerName, Path).
I use a Dynamic Properties task as the 1st step of each DTS package. It looks at c:\ini\sqlvalues.ini to set the ServerName property of each of my sql connection objects. I also set the path of all source & destination files in this step.
Maybe you can do something similar and look at the value of a parameter in an .ini file to determine what to do in your package.
July 17, 2006 at 3:45 pm
Yeah I was going to go down the same route, thanks for spelling it out though. The only other issue is that the 3 different instances are on the same machine (I know!!) so I was going to cut the ini file idea out and just use the table lookup method.
Thanks for your help.
August 8, 2007 at 9:26 am
Hi
you could still use
SQL Function "select @@SERVERNAME" which will return the current server.
if you execute it against a connection set to {local} , which is fine as long as you schedule the jobs and they run on the server itself. If you run it manually it will try to connect to SQL on your PC and return that name instead
regards
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply