There are 3 packages running on my server which are similar except for
the path that an executable runs at. If I right click the package in
Enterprise Manager and execute it, it fails. If I go into design mode
and execute it, it intermittently picks the wrong path to execute on
which I can see when the command window opens up on execution.

If I go into the properties of the task and reselect the executable,
and save, then the correct path is used. Any ideas why it appears to
be caching the wrong path? The path is from one of the other
packages.

I'm running on:
Microsoft SQL Server 2000 - 8.00.760 Build 3790

Any ideas why the path appears to be cached incorrectly? I'm
accessing the box that the SQL server runs on through Remote Desktop
Connection.

Next step is to access the packages through their interfaces and query
the properties of the tasks before executing them.

In article <8cc559ed.0312151036.13ce3cd (AT) posting (DOT) google.com>, Dave
<googlegroups (AT) rugh (DOT) org> writes
If you load a package and change a property anywhere in that package it
is changed, but will not be persisted further than that instance of the
package object scope, unless you save the package.

If a Parent package calls a Child package and passes in a new global
variable for example, the child will use the variable as set by the
parent there and then. If the child is then executed on its own it will
use the value of the variable when the package was last saved by you,
regardless of any subsequent executions and variable updates via the
Parent, as there was no save of the Child by the parent after the value
was changed, hence it not persisted outside the scope of that Parent
child instance.

--
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

Yes - that's the odd part of it. I'm not actually changing the path,
for some reason I have to touch it to make it work; more specifically,
I just reselect the executable from the properties window - the
directory path is correct.

I verified through the DTS COM interface that the command line was
correct; so it must either be a caching problem or a rights problem:
'***********************
sDTSName = "Translations"
oDTSPackage.FailOnError = True
Set oDTSPackage = New Package2
oDTSPackage.LoadFromSQLServer sDTSServer, sDTSUID, sDTSPWD,
DTSSQLStgFlag_Default, , , , sDTSName
Dim task2 As DTS.CreateProcessTask2
Debug.Assert "DTSTask_DTSCreateProcessTask_2" =
oDTSPackage.Tasks(6).CustomTask.Properties(1).Valu e
Set task2 = oDTSPackage.Tasks(6).CustomTask
Debug.Print "US:" & task2.ProcessCommandLine
Set oDTSPackage = Nothing

In article <8cc559ed.0312171025.470c5cc4 (AT) posting (DOT) google.com>, Dave
<googlegroups (AT) rugh (DOT) org> writes
???

There is no caching in Dts.

It is possible for you to execute an old version of a package from the
list view of packages in EM, since if it has been changed since you
refreshed the list view, when you right click the list view and select
execute it is reading the now old version guid from the list view data.
Not sure if this is exactly the mechanism, but it does explain some
behaviour I have seen.

Only other relevant issue that can cause confusion is that Dts is a
client side component, so a path to C:\Fred.bat is read from the C drive
of the machine which is running EM, regardless of the machine which runs
SQL Server and may or may not be sed to store the package. Doesn't
really explain the intermittent nature though.

The same local issues will effect the permissions, or execution context
as well. This doesn't sound right either.

Only thing I can suggest is to add some logging to your package top try
and help isolate the problem. I would set a package log file or logging
to SQL Server, and then use an ActiveX Script Task and the DTSPackageLog
objects WriteStringToLog or WriteTaskRecord method to write out the
ProcessCommandLine property. Check what this is each time you execute
take it from there.



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

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