Hi all,


I am successfully executing a DTS package from a VB app on the
development workstation, but having issues when trying to execute from
another client workstation...


The DTS connects to an .xls table, drops an existing SQLServer2000
table, creates a new (copy) SQL table, then finally populates the table
from the .xls file.


This same exact process works just fine for another set of SQLServer
Tables and .xls files on a network file server, but for some unknown
reason this one fails.


No errors are raised, but what happens after the process runs is that
the table gets dropped (as it's supposed to), then re-created (as it's
supposed to). But NO data is inserted from the originating .xls table.


Any insight/comments are greatly appreciated.


james


VB Code:


Public Function RunChickenPackage()
'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
Dim objPackage As DTS.Package2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objExecPkg As DTS.ExecutePackageTask


'On Error GoTo PackageError
Set objPackage = New DTS.Package
'Set mobjPkgEvents = objPackage
objPackage.FailOnError = True


'Create the step and task. Specify the package to be run, and link the
step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
With objExecPkg
.PackagePassword = "user"
.FileName = "H:\Shared\GIS\AO_VCMS\db\MonitoringPositives.dts"
.Name = "ExecPkgTask"
End With
With objStep
.TaskName = objExecPkg.Name
.Name = "ExecPkgStep"
.ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask


'Run the package and release references.
objPackage.Execute


Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
'Set mobjPkgEvents = Nothing


objPackage.UnInitialize
End Function

Hello jamesfreddyc,

Perhaps the package is failing silently. Can you set it to fail on first
error? Can you lop throught the steps at the end and find their execution
result. Remember this XL file must be visible from the client PC as DTS
is a client application and everything is based on the location of execution.
If the file is on a share somewhere then use UNC NOT mapped drives as well.


Allan

Allan,

Thanks for the reply...

1. Yes I can set it to fail on first error.
2. Not sure what is/how to loop through the steps -- could you explain?
3. The file is visible from the client PC
4. How to use UNC NOT? Where is this found?

Also -- as I had mentioned, this same exact process (and VB code) is
used on 3 other DTS packages, with the same client PC, on the same
mapped network drives. So, I am a bit confused as to why this process
works with other DTS packs/ same VB code...

Thanks again,

j

Hello jamesfreddyc,


You can see an example of looping through the steps here

Execute a package from Visual Basic (VB)
(http://www.sqldts.com/default.aspx?208,2)


instead of the filename and path being something like

M:\MyFolder\MyFile.xls

you should use

\\ServerName\Sharename\MyFile.xls

Allan,

This solves a huge problem for me -- thanks a bunch...

james