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.

My vb-script is pretty damn-simple. As I said earlier on, I use it to loop
around the pkg till I've finished archiving all my tables. Code below:
Function Main()
if (DTSGlobalVariables("counter").Value <=
DTSGlobalVariables("tablecount").Value) Then
DTSGlobalVariables("counter").Value = DTSGlobalVariables("counter").Value+1
'MsgBox "The table i'm doing is..." & DTSGlobalVariables("tablename").Value
DTSGlobalVariables.Parent.Steps("DTSStep_DTSExecuteSQLTask_2").ExecutionStat
us = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
else
DTSGlobalVariables("tablecount").Value = 0
DTSGlobalVariables("counter").Value = 1
DTSGlobalVariables("tablename").Value = ""
DTSGlobalVariables("bcpoutarch").Value = ""
DTSGlobalVariables("bcpinarch").Value = ""
DTSGlobalVariables("bcpoutstg").Value = ""
DTSGlobalVariables("bcpinstg").Value = ""
DTSGlobalVariables("finish").Value = 1
Main = DTSTaskExecResult_Success
end if
End Function

More thoughts????
Regards,
Uday
.....................................
Direct: +61-2-98059739
Mobile: +61-401 705907
Email: [Email address protected]




-----Original Message-----
From: Pollock Kirsty
[mailto:mssqldba-ezmlmshield-x91328702.[Email address protected]
Sent: Thursday, 25 November 2004 7:14 PM
To: LazyDBA Discussion
Subject: RE: Problems with DTSRun[Scanned]



Hmm, I haven't got much at the moment.



You have a consistent problem, at least, as scheduling a job sets up a

DTSRun command, and your problem occurs when doing DTSRun manually.



I take it there are no obvious errors reported, just the incomplete

results?



Is it possible that you could take the Active X task code out into a

development tool and step through it to see if you can spot the problem

(or get someone to do this for you?). I mean anything that does VBA

would do. It's a damn shame (polite version) that VBScript doesn't allow

proper error trapping, but that's how it is and we have to live with it.



If you (or anyone available at your office) is handy with VB(5 or 6)

then you could get them to knock together a program WITH proper error

handling that does the same loop calling DTSRun to try to see what's

going wrong with it(or even going on!).



You could try sticking the profiler on, and you might get *some* hint,

but probably not (hey, I'm a pessimist).



If you post the Active X script, we can try to see if there's anything

obvious, but I don't think the problem is there by fact that it runs

fine when executed manually.



This sounds rather similar to my DTS/C# woes - hopefully you will

actually find a solution!




MS Sql Server LazyDBA home page