Using global variables in DTS task

Posted on 2005-03-08
Medium Priority
Last Modified: 2013-11-30
I have a simple DTS task in SQL Server 2000 to update the data in a field, (using "Execute SQL task") but I want to create some other SQL tasks within the DTS to update data in other fields, based upon a predefined date.
I'm trying to define this date as a global variable that all SQL tasks can reference, but it hasn't worked as yet.(using Package Properties >> Global variables tab)
Ie; If I have 3 "Execute SQL tasks" with a date variable in them, I want to specify the same date to be used in each task (without having to edit each task) ---really appreciate help!

Question by:fjkilken
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 43

Expert Comment

by:Eugene Z
ID: 13486556
check the article and web site:

Accepted Solution

lozzamoore earned 2000 total points
ID: 13486563
Hi Fjkilken,

Set the Global variables up in the package using Package-Properties. Be sure to set the datatype to "Date".

You know have a couple of options, depending on the complexity of what you are doing.

In your SQL Tasks, use ? in place of the date where you want the Global Variable to be placed.
eg. update table1 set thedate=?
Click the parameters option and tie this ? up to the global variable you created above.

Use the "Dynamic Properties" task to edit the SQL within the Execute SQL tasks and set up a success workflow dependency so that this is run before the SQL tasks themselves.

For simple substitution I always use 1, for more flexible options, use 2.

Don't forget, if you are calling the package from the command line, you can "overwrite" the default value for the Global variable using the /A options,  e.g  dtsrun /N"package name" /Aglobal_variable:7:2005-01-01 09:00:00.000'

Hope this helps,

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 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