Sql Server 2000 DTS - Automatically Update global variable

Posted on 2010-01-06
Medium Priority
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
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26192683
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

bkadaba earned 1000 total points
ID: 26197901
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

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

Author Closing Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 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