Uday,

I must have been unclear. I was under the impression that when you use a
DTSRun commmand, then the behaviour is exactly as when your package runs
as a scheduled job (i.e not right) and that when you manually execute it
(i.e. right click and 'Execute Package' or hitting the 'play' button in
the DTS Designer, then all is well. Is this an accurate statement of
what happens?

Anyway back to the code....

It's not a loop. I can't see how you expect this to ever do more than
one table!

Use a for loop


E.g

dim counter

For counter = 1 to DTSGlobalVariables("tablecount").Value

'call stored proc or whatever
Next

Main = DTSTaskExecResult_Success


I don't think that what you are doing in setting the
DTSStep_DTSExecuteSQLTask_2 status works to do what you want.

I'd directly call the sql/sp that's in your SQL task in the code - yo
need to set up ADO connection and command objects to do this - you can
look up how to do that on BOL or MSDN or the 'net.

Kirsty

> -----Original Message-----
> From: Uday Shivamurthy [mailto:[Email address protected]
> Sent: 25 November 2004 11:32
> To: Pollock, Kirsty
> Subject: RE: Problems with DTSRun[Scanned]
>
> **A LazyDBA.com subscriber has responded to your lazydba.com
> post** **LazyDBA.com mail shield has forwarded you this
> email, **and removed any attachments, and kept your email
> address secret **from this person, and any viruses/trojans.
> **If you reply to this email, the person will see your email
> address as normal **Anything below this line is the original
> email text
>
>
> Hi Kirsty,
>
> Actually I do *not* have a problem when I execute the dts pkg
> manually. The problem is when sql-agent tries to execute it,
> the looping in my pkg does'nt work and it just quits after
> one execution.
>
> No, there are no obvious errors.
>

MS Sql Server LazyDBA home page