Using global variables in DTS task

HI,
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!

Fergal
fjkilkenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
check the article and web site:
http://www.sqldts.com/default.aspx?205
0
lozzamooreCommented:
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.

1)
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.

2)
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,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.