Executing a DTS Package from Access 2003 (Visual Basic)

[Don Hicks]

I am trying to execute a SQL Server DTS package from Access 2003 using the
following code.

Dim mobjPkgEvents As DTS.Package

'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 = "CORP\hicksdl"
'.FileName = "T:\hicksdl\act_dev\sap_file_import.dts"
.Name = "sap_file_import"
End With
With objStep
.TaskName = objExecPkg.Name
.Name = "sap_file_import"
.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

Exit Function

packageerror:
MsgBox "There was an error executing the import.", vbCritical
MsgBox Err.Description
Resume HandleButtonClick_Exit

I get the following error message.

"Invalid Authorization Specification"

I can't seem to find any good documentation on what the various attributes
(Name, Pasword, etc.) should be set to. Can someone steer me in the right
direction. The package is stored in the database and I'd like to execute it
from there. Thanks in advance for your help.

 

[dbahooker]

oh dude..

I would reccomend:

a) firing this via XP_CMDSHELL from _THE_DB_SERVER_
I just don't think that you will necessarily have the correct tools
installed on your desktop
b) even if you do have the client tools on your desktop; you need to
make sure you have the correct version of clientside tools (SP3, Sp4
for example)
c) DtsRunUI will allow you to select a DTS package; and then build a
command line argument for it. Doing it without DTSRUNUI is a PITA

hope that helps; i love DTS and I love Access.
I just wish that the DTS tools were INCLUDED with Access.. I mean they
really should be.

-Aaron

 

[dbahooker]

a) I really don't follow what you're doing here

.PackagePassword = "CORP\hicksdl"
'.FileName = "T:\hicksdl\act_dev\sap_file_import.dts"
.Name = "sap_file_import"
Can you plz explain? I mean; is your password really the same
as your useraccount?

b) you've got to make sure that the service account that is running
this is going to be able to use a mapped network drive-- I would
reccomend UNC everywhere

c) you can take a package and save it as a vbScript file.. I think
that migth give you a nice couple of pages of code sample.. so instead
of pointing to the package; you can include the source for everything
inside a module.. I _LOVE_ being able to take a package and save it as
Vbscript

d) the thing about DTS is that it's designed to run from a SERVER and
I'd reccomend figuring out how to run it from the server

e) running it from a desktop is inherently less efficient (depending
on the circumstance)

f) you might even have some double-hop authentication problems running
this from a desktop that points to another server that points to yet
another server. please refer to 'please run from the server'

thanks I hope I help; if you want more questions; and I'm available
(not very likely, but it depends on things like the stock market; the
weather and the price of beans in boston) (e-mail address removed) is
my MSN Messenger logon


-Aaron