Sql Server 2000 DTS - Automatically Update global variable

Posted on 2010-01-06
Last Modified: 2013-11-30
I have a DTS package that uses the computername as a global variable.  I would like to update the global variable automatically from sql query analyzer or another method.
Any suggestions?

I just need to update this once.
Question by:LovetoLaugh
    LVL 75

    Expert Comment

    by:Anthony Perkins
    So you are trying to update the global variable with the computer name?  Which computer ?  The one that is running the DTS package or the server that has MS SQL Server?

    In either case this can be done in the DTS Package, by creating a task to set the global variable prior to its use.

    Accepted Solution

    This is the approach I tried in one of the projects.

    Executing DTS PAckages with xp_cmdshell

    The xp_cmdshell extended stored procedure is available in the master database. The command line structure to execute dtsrun is:

    [/?] |
             /[~]S server_name[\instance_name]
             {  {/[~]U user_name [/[~]P password]}  | /E }
              {/[~]N package_name }
              |  {/[~]G package_guid_string}
              |  {/[~]V package_version_guid_string}
          [/[~]M package_password]
          [/[~]F filename]
          [/[~]R repository_database_name]
          [/A global_variable_name:typeid=value]
          [/L log_file_name]
          [/W NT_event_log_completion_status]
          [/Z] [/!X] [/!D] [/!Y] [/!C]

    Refer SQL Server Books online for more details:

    E.g. This is the sample example for dtsrun

    dtsrun /Sserver_name  /Uuser_name  /Ppassword  /Npackage_name  /Mpackage_password
    /A "customerID":"18"="45"

    18 - is the ID of the global variable data type and 45 - is the value passed to the global variable - CustomerID

    * The ID for the string datatype is 8

    Multiple global variables can be used for which multiple /A switches to be used.


    Expert Comment

    Sorry forgot to mention, dtsrun can be used in the SQL Analyzer tool.

    Author Closing Comment

    This is what I am looking for.  I haven't tried it yet.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how the fundamental information of how to create a table.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now