Post reply

Current Server/Instance for DTS?

  • June 9, 2006 at 12:57 pm

    #68535

    Does DTS run under a particular instance?  I have a package that I'm designing for a server with multiple SQL instances.  I have a dynamic properties task that sets the data source for a few connection objects.  I'd like it the Dynamic Property to be able to set the connection to the current instance - if such a  thing exists.  For example, does it matter which instance I expand in E.M. if I right-click and open a DTS package from a storage file?  And then, I want to know if the same thing is true for SQL Agent.  Is there a way to get the instance for the current context?
     

  • June 10, 2006 at 12:30 pm

    #642975

    The package runs in whichever instance you're connected to when you open the package and execute it.  The same is true for SQL Agent.  The package will run on whichever instance the scheduled job is running.

    Use this query to find the instance name:

    select serverproperty('instancename')

    Here's a good article: http://qa.sqlservercentral.com/columnists/tdavid/moreportabledtspackages.asp

    Greg

    Greg

  • June 12, 2006 at 10:56 am

    #643184

    This does not work for me.  I have a server with a Default (unnamed) instance and a named instance.  Even the article you mentioned seems to leave out a key piece of information: the initial value for Server in the Connection object at design-time.  I tried both "(local)" and ".", but they both come back with the default instance, which is essentially the machine name.  How do you get around this, since every Execute SQL task requires a specified connection?  It's a vicious cycle.
     

  • June 12, 2006 at 1:35 pm

    #643221

    >>It's a vicious cycle.

    Welcome to 1 of the major issues with DTS. You need to execute SQL to find your "current instance", but you can't execute SQL without a connection, and you can't dynamically point your connection without executing the SQL to find your instance.

    Some of the workarounds:

    - Dynamically configure via an INI file with a hard-coded location

    - Run all your packages via xp_cmdshell running DTSRun.EXE and passing instance name via command line setting of DTS variables

     

     

  • June 12, 2006 at 1:50 pm

    #643230

    Although comforting to think that I'm not just a dufus who can't figure this out, I'm bummed that nobody has offered a magic bullet to fix this problem.  I was really hoping to avoid introducing something outside of the package to get this to work.

    Thanks for the input guys!
     

Viewing 5 posts - 1 through 4 (of 4 total)

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