Parameter in DTS package

Question

Parameter in DTS package

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
August 8, 2002 at 5:05 pm

#158629

I am trying to setup a parameter in DTS package using SQL task.
When I use question mark (?) for the paramenter and execute the task I get nothing. I should be prompted for the parameter.
appreciated if any one can help me.
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

You can click the parameters button to bind ? to a global variable. "I should be prompted for the parameter" I don't think this is the case, why do you think you should be prompted?

Regards,

Andy Jones

.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Then how can we pass the value through parameter.

let's say , I put the following query in the SQL TASK.

select * from tablename where field = ?

and then I assign this parameter to the Global variable .

so when I execute SQL TASK don't you think that I should be prompted to input the value in the paramter.

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 3

Use an ActiveX task, write a script in vbscript or whatever scripting language you use, and via an input box prompt for the parameter value to be entered...as far as I know, there is no other way.

script example:

Function Main()

Dim myval

myval = InputBox("Enter parameter")

If len(myval) > 0 Then

DTSGlobalVariables("myparameter").Value = myval

'put function dts success flag here

Else

'put dts failure flag here

End If

End Function

hth,

Michael

Michael Weiss

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 4

Sorry, I should have been more specific (its been a long day!)...using an input box to prompt for the parameter value to be entered is how you set the value of the global variable that you are passing to your sql task...hope that makes sense!

hth,

Michael

Michael Weiss

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

Thanks Michael,

I think ActiveX is the only way to pass the value in the parameter.

Thanks for your help.

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 6

You are most welcome...the input box method of prompting for a parameter works great for me and I use it extensively...good luck with your project!

Michael

quote:
Thanks Michael,
I think ActiveX is the only way to pass the value in the parameter.
Thanks for your help.

Michael Weiss

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 7

Gloabal variables are also configurable when executing the package. When running from the command line use "dtsrun /A" or there is a global variables collection property of the package class if using the com interface.

Regards,

Andy Jones

.

philcart SSC-Forever Points: 47796 More actions · Answer 8

Another method is to use the Dynamic Properties task. Using this method you can set Global Variables from an INI file, a database query, an environment variable and a couple of others as well

--------------------
Colt 45 - the original point and click interface

Deuce SSCrazy Points: 2712 More actions · Answer 9

If you're executing the DTS package from VB, you can also access the global variables from the object model.