ActiveX to execute Exec SQL task

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 04 Aug 2005 18:56 GMT

I have an activeX task that performs a loop and at the end of every loop i
want it to execute the exec sql task. I have tried:
Set objSQL =
DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
.....
objSQL.Execute

But i get an error "wrong number of arguments or invalide property"

How can this be done? The reason im doing it this way is that the query the
exec sql uses is dynamic to the current loop in the activeX control

thanks for any and all help!
Ben

Reply to this Message

Peter A. Schott - 04 Aug 2005 20:13 GMT

Why not set the code for the ExecSQL using the Dynamic properties task and put
that right before the loop back to the beginning? That should work.

If the only change in the code is a parameter or piece of the where clause,
you may also be able to use Global Variables and the parameters function of
the Exec SQL task by placing a "?" every place you want to use a global
variable and then assigning those with the Parameters button.

-Pete

> I have an activeX task that performs a loop and at the end of every loop i
> want it to execute the exec sql task. I have tried:
[quoted text clipped - 10 lines]
> thanks for any and all help!
> Ben

Reply to this Message

Allan Mitchell - 04 Aug 2005 20:29 GMT

You can either wrap the statement into your Active Script task or you could
possibly adapt these

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)

Signature

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

>I have an activeX task that performs a loop and at the end of every loop i
> want it to execute the exec sql task. I have tried:
[quoted text clipped - 11 lines]
> thanks for any and all help!
> Ben

Reply to this Message

Ben - 04 Aug 2005 20:45 GMT

thanks for both your answers. But i think you might have missed my question.
When i attempt to execute the exec sql task, it get the aboved mentioned
error. what are the parameters its wanting? I would perfer if my code didnt
change much, I would like the activeX scipt to invoke the other tasks without
worriing about "on success" options. I could easily do this with an ADO
connection, but i want to attempt it this way as well. It gives it a more
visually appealing look.

Thanks again
Ben

> You can either wrap the statement into your Active Script task or you could
> possibly adapt these
[quoted text clipped - 20 lines]
> > thanks for any and all help!
> > Ben

Reply to this Message

Allan Mitchell - 04 Aug 2005 21:32 GMT

I have never called the execute method on the task from inside another task
and do not know anyone else who does either.

You can though find the parameters you require in BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\dtsprog.chm::/dtspmethai_4p0k.htm

Signature

> thanks for both your answers. But i think you might have missed my
> question.
[quoted text clipped - 37 lines]
>> > thanks for any and all help!
>> > Ben

Reply to this Message

padavala - 25 Jun 2009 22:11 GMT

Hello,

Sorry for troubling you after many years.Did you find the solution for this?

I need exactly same functionality now.
Thanks.

url:http://www.ureader.com/msg/11461238.aspx

Reply to this Message

matteog - 26 Jun 2009 10:48 GMT

> Hello,
>
[quoted text clipped - 4 lines]
>
> url:http://www.ureader.com/msg/11461238.aspx

I see no reason for DTS letting you do this, but I cannot say for sure
it's not possible...
I guess this could bring you to break precedence constraints on tasks'
workflow.

> I could easily do this with an ADO connection

That's the solution, IMHO
Bye,
M.

Reply to this Message