First global var 2

[SQLScholar]

Hey all,

I have been wading through this forum looking to work out how to use global variables.

I have a DTS package that has about 10 SQL queries inside it.

Each one uses getdate(). What i would love to do is replace the getdate() with a global variable.

Normally this variable would be getdate() - but if there is an issue and i need to re-run, i could modify the var to any given date.

How do i begin to do this? I have been fiddling with DTS for a while, and looking at this forum - and i just cant sus it out!

Thanks

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[AlexCuse]

The way I normally handle something like this is to write a little VB front end that I can open up, specify the gv's value in a text box, and then execute the DTS from there (passing in the value as a global variable). I have not done this with dates, but I do it to specify different file names I want to load and what not.

If you don't want to run it this way, I would look into setting up a script task that will prompt the user with an input box to enter the date (and of course ask again if an invalid date is entered), and set that as your global variable before continuing with the package.

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

I forgot, once you have a global variable you can access it by typing a ? where you wnat the parameter in your query, and then click on 'parameters' button.

Now you should have everything you need, let me know if you run into any problems.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[nice95gle]

AlexCuse is on the right track with the ?

But you don't need to write a vb front end to change a var. Do a little reading on the "Dynamic Properties task" and .ini/txt files. If you run into a problem or have a question let me know.

 

[AlexCuse]

nice95gle - you're right, I always overlook these. I have always disliked the dynamic properties task, I find them rather clumsy to work with. Can it be used to prompt the person executing the package for input, and then assign global variables that way?

I have always done this via activeX tasks, but I am always looking to learn new things (although with SSIS coming in my near future I wonder if it would be a good use of time?)

Ignorance of certain subjects is a great part of wisdom

 

[nice95gle]

Yes they are rather clumsy. Dynamic Properties task shine when it comes to migration from Dev to UAT to Prod. One stop shopping; change your ini file and you’re done (if you use your ini file to hold your connection properties). But to answer your question: with the use of input boxes in your activeX object you can prompt the user for input. Then write the response to the ini file and the rest will fall into place. But remember you can also use the input boxes to change the Global variables without the use of an ini file and the Dynamic Properties task.

Dim X        
X=INPUTBOX ("what is your question?")
DTSGlobalVariables("my_Global_Var").Value=x

There are many ways to get to point B.

 

[AlexCuse]

There are many ways to get to point B.

Yes, this is the beauty of DTS (and a source of much confusion at times ;-) )

Ignorance of certain subjects is a great part of wisdom

 

[SQLScholar]

YEY!

Thanks so much all - i had my first sucess run last night.

STARS ALL ROUND

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[AlexCuse]

That's awesome Plank. I think you will find that you can make your packages much better by taking advantage of global variables

Ignorance of certain subjects is a great part of wisdom