Execute DTS from VB

Question

Execute DTS from VB

slanky2

SSChasing Mays

Points: 640
More actions
November 27, 2002 at 8:08 am

#79950

I am attempting to create a procedure that executes a DTS package from a VB app. The package was initially created by another one of our administrators and the owner is listed as his NT Username. If I run the procedure from my machine, or any other machine that I am logged in to, VB app works fine and the package is executed, but if one of our regular user's tries to run the procedure the package will not execute.
Here is the code that I am using:
oPackage.LoadFromSQLServer "Servername", "sa", "password", 0, , , , "Package Name"
I belive that this must be some sort of permissions issue and that is why I was using the sa account to try and execute the package.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Are they being logged in under sa inside the app or their own account. If their own then try setting the user password for the DTS package and running. There was another thread on this a few weeks ago.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Don't use sa. Create another user and use that, give it permissions to run the job.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 3

If you don't need to store the package locally, you can save it as an external file, place it on a network drive and then use DTSRUN to execute it. that might circumvent the access problems you are seeing.

External packages are a little more difficult to manage so use this only as a last resort.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Again go back and look at the syntax of LoadFromSQLServer

Package.LoadFromSQLServer ServerName, [ServerUserName], [ServerPassword], _

[Flags], [PackagePassword], [PackageGuid], [PackageVersionGuid], _

[PackageName], [pVarPersistStgOfHost]

Set a user password on the package and save it. Then set the parameter PackagePassword with the user password and it should run just fine.

Also read the section in BOL "Handling Package Security in DTS"

rvanderpal SSC Journeyman Points: 96 More actions · Answer 5

Perhaps giving the transaction server's user right's on the package will help.