ActiveX: To use a Value from an Inputbox as a Global variable in the SQL Task. -- sql server dts

kris
7/13/2004 12:29:33 PM

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.

[quoted text, click to view]

anonymous NO[at]SPAM discussions.microsoft.com
7/13/2004 2:26:09 PM

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.

[quoted text, click to view]

Allan Mitchell
7/13/2004 8:21:21 PM

[quoted text, click to view]

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

Allan Mitchell
7/13/2004 9:06:21 PM

[quoted text, click to view]

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

Allan Mitchell
7/14/2004 6:37:46 AM

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

Kris
7/14/2004 12:02:37 PM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : ActiveX: To use a Value from an Inputbox as a Global variable in the SQL Task.