Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
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

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

577 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