Hi all,
I need some help here.
I want to create an ActiveX with an Inputbox = ("Enter a
number")in a DTS Package. Value entered in the input box
at the runtime should be passed to Global
Variable "gvweek" as Input Parameter. I want to use this
GV.Value as a condition (e.g. Where week_no = ?)in my
script in the SQL TASK ("DTSStep_DTSExecuteSQLTask_1").

I did this, but its not correct. can anyone help me.

Function Main()
Dim oStp, oVAR, oVAL
Set oStp = DTSGlobalVariables.Parent.Steps
("DTSStep_DTSExecuteSQLTask_1")
oVAR = (DTSGlobalVariables("gvweek").Value)
oVAL = inputbox("Enter A Number ")
Set oVAR = oVAL
If int(DTSGlobalVariables("gvweek").Value) >= 1 then
oPkg.DisableStep = True
else
oPkg.DisableStep = False
end if

Main = DTSTaskExecResult_Success
End Function


Thanks
Kris.

In article <2cad101c46901$93cb8950$a301280a (AT) phx (DOT) gbl>, Kris wrote:
The package object does not have a DisableStep method.

Also, I do not consider DTS the right choice for user interaction. I
personally would rather I asked the user up front of executing the
package for any values they needed to supply.

Also why do this

oVAR = (DTSGlobalVariables("gvweek").Value)
oVAL = inputbox("Enter A Number ")
Set oVAR = oVAL

Can you not do

oVAL = inputbox("Enter A Number ")
DTSGlobalVariables("gvweek").Value = oVAL


You may want to do some checking of the value supplied also.



Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Actually! I modified it as follows and it worked.

Function Main()
dim conn, var
set conn = DTSGlobalVariables.Parent.Steps
("DTSStep_DTSDataPumpTask_1")
DTSGlobalVariables("WKADD").value = InputBox("Enter
Current Month as 1, 2, 3, etc.")
Main = DTSTaskExecResult_Success
End Function

The value from the inputbox is now in the GV. I am trying
pass this into the Execute SQL Step (Script) as a
condition.

Thanks again.
Kris.

In article <2cbf401c4690f$b9df1f90$a301280a (AT) phx (DOT) gbl>, Kris wrote:
Are you now asking for this next part of the puzzle?

Assuming you are using SQL Server 2000 then you statement will look
something like

SELECT col1, col2 FROM table WHERE col3 = ?

You will then see the parameters button which you can use to assign
your GV to the ?

If you are using SQL Server 7 (or if you want to do it this way, SQL
Server 2000) then you can do it also like this


Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Hi,
Thanks for your help. I really appreciate it.

I have a variable in my SQL statement. I am trying to pass
this value from the GV into that.
Like, set @wk_no = ? , for which I want to define the
parameter as the GV. The statement could not be prepared.

If I use
"Select wk_no from time_input where wk_no = ?"
or
INSERT INTO time_input(wk_no ) VALUES (?)
as a single statement, it accepts.

I dont know! It looks I can't use ? in a statement where
it has BEGIN... END. or may be to another variable.

Kris.

I do not know why you would need to do this as an assignment.

You can get around the delicacies of the design time interface but show
me the whole statement and we can work something out.


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Hi,
Thanks for replying.
I found a workaround for this and it works now.
I wanted to make this dts pkg little interactive becoz
this method will be useful to change fiscal rolling
periods dynamically based on the user input. Now user
(developer)can rollback no. weeks by simply entering the
value in the box. it calculates the no of days to rewind
and rolls back to that period. For e.g. in the 2nd week of
july, if the users enters 2, it rolls back two weeks and
sets the indicator to June as the current month. There are
100's of tasks based on the this Indicator. There are
transformation indicators like, TM (This Month) , LM
(Last Month), LLM (Last To Last Month), TMLY (This Month
Last Year) ... so many. They are on a rolling basis. i.e
Aug 04 will become TMTY from July the next month. They
roll automatically based on the regular and fiscal
calendar just by taking one input (System's Date)

On special occassions, it is required to present last
month's data as the TM's for the first few days, may be A
CASE when the new fiscal year starts.

Now I have made the ActiveX to take the input, calculate
the no of days and store it into a GV. The other
transformation program takes this value sets the indicators
And all the backend jobs follow.

I am very new to this ActiveX world. I am learning some
now.
Thanks for your help.

Kris.