Can someone provide a clear example on how to initialize the
parameters that are required for the task execute method:

object.Execute pPackage, pPackageEvents, pPackageLog, pTaskResult

BOL doesn't has not been helpful. The answer may be there somewhere.
but I can't decipher it.

Thanks,
JP

In article <1f323b67.0310130607.7701da53 (AT) posting (DOT) google.com>, JP
<SocSecTrainWreck (AT) earthlink (DOT) net> writes
You don't need to. From Books Online - "An application does not have to
call the Execute method for each step or task. Instead the Package2
object calls the Execute method to launch each step or task after the
application calls Package2.Execute."

Does this help?

If not why not, and what are you trying to do?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote

No. I already knew this. I also already went thru the archives and saw
the previous discussions on this topic.

I am trying to create a task within a ActiveX task so I can package
the functionality of another task within a single task that I can swap
out of a large number of packages. To be specific, I am trying to
replace an Execute Package task (which is junk, IMO) with an Execute
Process task call to dtsrun, and do so in a way that allows for
dynamic configuration of the commandline (/A)global variables that
will be passed to the inner package, all within a single task. I don't
think that package2.execute is going to execute a task that does not
exist before or after the task that creates it is executed. So, does
anyone know the answer to my original question? Is it possible to call
task.execute from within a ActiveX task, and if so, how? I can tell
you that it *is* possible to create the task and destroy it from
within an ActiveX task; I have demonstrated it. (I can run the script
and then see the new task in disconnected edit of dts designer, and
then change the script to destroy it and verify that the task is
gone.) Now I just need a way to execute it. So close yet so far.

I suspect that there may be a difficulty connected to using VBScript
with pointer variables, but it's hard to tell from the documentation.
Is this the problem? Is there a workaround?

There may be some other way to do this, but I don't think that what I
am trying to do is unreasonable, assuming that I have not stumbled
across yet another broken piece of dts.

Thanks,
JP

You may be able to add and remove tasks dynamically, but it is not
supported. I do not know of any documentation to support this, but I was
advised of this by a PM from the DTS team. What you describe is not really a
valid test either as by using Disco Edit to confirm that it has worked means
that you are no longer in a run-time environment.

If you add a task, this in itself corrupts the package. A task must be
associated with a step. A step has an Execute method which is for internal
use only, but has been used at run-time explicitly. Again this is not
supported. If you really want to execute a task in the way you describe then
the step execute method would do it.

The whole idea of adding tasks and steps at run-time means you run the risk
of breaking the internal scheduler that reads workflow and similar
information to decide when to execute a task. What would be a better way
would be to have the task and step already in existence and change the task
properties at run-time to make it do what you want. If you don't want to use
a given template task then you can also disable the step for that execution
instance.

Personally I find the parent child model more than sufficient for producing
modular systems based on DTS, but the template task idea might work better
for you.

--
Darren Green
http://www.sqldts.com

"JP" <SocSecTrainWreck (AT) earthlink (DOT) net> wrote

"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote

What do you mean it is not supported? I found the programming objects
to do it in the documentation. I've never heard of such a thing.
"We'll make this programming interface available to you but if you
have the temerity to ask any questions about it we'll call it
unsupported." If it's in the docs with no disclaimer, it *is*
supported!

What a copout. But coming from the DTS team it's not surprising.
Rather than fix the software, or admit that we left a hole in the
documentation, we'll just call it "unsupported". What did they say
when you asked them how to call task.execute from VBScript? Sounds
like they might have shifted the discussion to the red herring of
runtime task creation so well that they sidetracked you.

Of course it is a valid test. Is it an absolute test? No. It *is* a
strong indication that the object was created. Whether it can actually
run or not is a different test that requires me to figure out how to
call task.execute, which is why I'm here.

That's an arbitrary determination on someone's part. I don't agree.

Why not use the task execute method? Why is a step needed? I really
don't want or need an additional step- there already is one associated
with the activex task that creates the new task.

I frankly don't see how. The scheduler probably won't see anything
except for the activex task/step.

Except for one thing: it doesn't do what I want, which is to package
the functionality of the process exec and activex tasks in one task.
MY UNDERLYING OBJECTIVE IS TO PACKAGE FUNCTIONALITY _TOGETHER_. A
process exec task configured by an activex task is not "together". Of
course, if the bloody exec package task worked reliably I wouldn't be
worrying about this. As it is, I will not use that task in future
development. It is too buggy, and the business cost of fixing packages
that rely on it after it stops working is too high to contemplate
further.

I have this weird belief that an object based model that allows you to
instantiate objects at runtime should not croak when you try to
instantiate objects at runtime. But in defense of DTS, there is no
evidence that it does croak. At this point the question is still: How
do you initialize the object references that are passed to the task
execute method from within VBScript? The only reason we are even
discussing this other stuff is because the documentation does not tell
you how to do it *in VBScript* and because the MS DTS PM doesn't know
how to do it either. (Not surprising, though, because PMs often are
not technically proficient with the internals of their projects.)

JP

In article <1f323b67.0310170628.5e4dc9dd (AT) posting (DOT) google.com>, JP
<SocSecTrainWreck (AT) earthlink (DOT) net> writes
The usage you describe is in my opinion unsupported. I tried explaining
my reason for this but you just saw that as another excuse to rant.
I entirely agree that the task method is documented and therefore
supported as an interface, but interfaces are documented for different
reasons. The reason I suggest that the method is documented is that in
order to write your own custom task you would need to implement the
Execute method. You are not supposed to call it yourself, but if writing
a component that is then hosted within DTS you need to implement the
required methods.

But the point is the scheduler only sees steps, not tasks. Precedence
constraints are related to steps not tasks. You can change the step
ExecutionStatus property to prevent a step being executed or to
re-execute a step. The evidence all points to the step being the
container that receives the initial execute event.

Script task to call the child package.

In between bitching about others, have you actually tried to use the
Execute method? According to the documentation pPackageEvents and
pPackageLog can be nothing, although you could probably use
DTSPackageLog for pPackageLog. You do not have access to any event
handler inside an ActiveX Script Task, so has to be nothing. This leaves
pPackage which is easy enough, DTSGlobalVariables.Parent, and the
pTaskResult which is a long, so just pass in 0. You cannot get anything
back from the task, not even pTaskResult since ActiveX Script Task
doesn't have string types which are required for ByRef parameters to
work both ways.


Option Explicit

Function Main()

Dim oPkg

Set oPkg = DTSGlobalVariables.Parent

Dim oCustomTask ' DTS.ActiveScriptTask

Dim oTask
Set oTask = oPkg.Tasks.New("DTSActiveScriptTask")
oTask.Name = "DTSTask_DTSActiveScriptTask_X"
Set oCustomTask = oTask.CustomTask
oCustomTask.Name = "DTSTask_DTSActiveScriptTask_X"
oCustomTask.Description = "ActiveX Script Task X"
oCustomTask.ActiveXScript = "Option Explicit" & vbCrLf & _
"Function Main()" & vbCrLf & _
" MsgBox(""ActiveX Script Task X"")" & vbCrLf & _
" Main = DTSTaskExecResult_Success" & vbCrLf & _
"End Function"
oCustomTask.FunctionName = "Main"
oCustomTask.ScriptLanguage = "VBScript"
oCustomTask.AddGlobalVariables = True

oTask.Execute oPkg, Nothing, Nothing, CLng(0)

Set oCustomTask = Nothing
Set oTask = Nothing
Set oPkg = Nothing



Main = DTSTaskExecResult_Success
End Function


This appears to work, in so much as I get the message box to show from
the dynamically created task.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote

I'm not really looking for reasons to rant. In fact, what I'm looking
for is solutions. But I have to admit that I am angry at being forced
to deal with such BS as MS calling a documented programming object
"unsupported", not to mention bugs in DTS that are unaddressed,
despite the fact that they have been reported *repeatedly*. I can't
tell you how many hours I have spent working around DTS bugs. Yes, I'm
pissed.

Completely beside my point, which was that the scheduler won't see
either if the task *or* step is created at exec time within an activex
script task.

I am considering that; however I don't have access to VB at the
moment, so I was trying to do it with VBScript. Installing VS.net is
yet more work to fix what is basically a bug with DTS (the broken exec
pkg task object) that MS should fix but won't.

Exactly what I'm trying to do, which recalls my question of how to
call the task execute method.

Uh, yes. I get a type error. Which I think is because I haven't
properly initialized the parameters to pass to it. Which is why I
started this thread.

Thanks. This is what I was looking for. I missed in the docs (where is
it?) that you could pass Nothing to the method. I tried casting a
variable with CLng() but did not try it with zero, and because I had
three problem parameters (as you indicated, pPackage was trivial)I
never knew what worked and what didn't.

I will try this and report back.

JP

"JP" <SocSecTrainWreck (AT) earthlink (DOT) net> wrote

<snip>
What I meant was to go back to using packages to encapsulate your
funtionality, but instead of using the Exec Pkg Task, use an ActiveX
Script
Task to do the same job, i.e. Load an existing package, then execute the
package.

See SQL Server Books Online, topic "Execute Method"
(mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books
\dt
sprog.chm::/dtspmethai_4p0k.htm)

"Check pPackageEvents and pPackageLog for NULL/Nothing before using
them."
This says to me that they can be nothing. The pPackageLog parameter has
always been there, since 7.0, but was never implemented until SQL 2000.
Makes sense that you can go backwards as well. The description is really
geared towards being inside the method, such as when creating a custom
task, as per my previous comments on should this method be used this
way. Still, if it works...

Type errors are always a pain in VBScript because you have no types. VB
might be better in the long run, and VB6 will suffice.

(Sorry if this appears twice, but it hasn't appeared after 7 hours so
far)
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote

I probably was not very clear in describing what I was trying to do. I
am still using inner packages, but since I have found the package exec
task to be unreliable I am trying to replace it with a commandline
call to dtsrun. I am using an activex task to construct the
commandline based on the same global variables that were previously
passed to the inner package using the (to be replaced) package exec
task. Then, after I construct the commandline, I create the process
exec task on the fly, load it with the commandline, (I hope) execute
it, and then destroy it. This way all of the functionality of the old
package exec task is packaged within the activex task, yet I do not
need to mess with the existing inner packages- I can just go through
the outer packages and replace the existing package exec tasks with in
each case a single activex task without disturbing the rest of the
outer package either. I don't know if that's the same as what you are
suggesting but I think it is somewhat similar. This is all based on my
observation that dtsrun does not appear to generate the "cancelled by
user" error that is always generated in the same outer package when it
is run in dts designer.

I took this to mean check for an error condition, but now that you
point it out, it does seem to imply that the method will allow those
non-values.

Still haven't had a chance to test it but I suspect it will based on
your results. Will let you know. Thanks.

JP

In article <1f323b67.0310210827.fd2e272 (AT) posting (DOT) google.com>, JP
<SocSecTrainWreck (AT) earthlink (DOT) net> writes

Based on my new understanding, I think you could use the ActiveX Script
to load and execute the package directly, as opposed to via DTSRUN.
Personally I dislike shell commands since I always have problems getting
the result back out. The ActiveX Script options aren't great, but does
give you basic info.

Simple example of using ActiveX Script to execute a child package-

Execute a package from a package
(http://www.sqldts.com/default.aspx?215)

Cheers
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org