Executing DTS package from Stored Procedure

Question

Executing DTS package from Stored Procedure

KeithDavies

SSC Veteran

Points: 267
More actions
January 6, 2006 at 9:22 am

#170473

Ok this is an old topic but with a new twist.
I'm trying to execute a dts package from a stored procedure. The dts package is a simple activeX script which moves a file from one location to another and deletes the original.
When I run the package directly in enterprise manager on the server it runs OK. When I run the package using xp_cmdshell and dtsrun from query analyser or a stored procedure the package fails when it trys to create the file system object using 'Set objScript= CreateObject("Scripting.FileSystemObject")'.
I believe this may be a permissions issue but any suggestions?

Regards

Keith Davies
IT Consultant

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

If all you are trying to do is copy a file and then delete the SOURCE have you thought about using a DOS BAT file that you could call from xp_cmdshell? Or a VB app that could look at an INI or something else.

Sounds like overkill to use a package like that...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

KeithDavies SSC Veteran Points: 267 More actions · Answer 2

Thanks for the reply, but can't use a dos bat file due to the security set-up - I think this maybe why xp_cmdshell may not be working correctly. The package and SQL Server has the permissions to do the task, it's just sorting a method which will work.

Regards

Keith Davies
IT Consultant

Peter Schott SSCrazy Eights Points: 9674 More actions · Answer 3

There's the option of starting a job that runs the DTS package through a trigger. Alternatively, set up a trigger to populate a table and a job that runs regularly to poll the table and kick off the DTS package if there is a new/updated row in the table. That will probably work for security.

xp_cmdshell probably has an issue with security - check to see what login it is using to access the file system. More than likely it's pretty locked down and restricted to the local machine.

Matt Matzek SSC Veteran Points: 215 More actions · Answer 4

I would take at this article from the Database Journal: http://www.databasejournal.com/features/mssql/article.php/1459181

The article describes how to execute a DTS package in a stored procedure without using the xp_cmdshell command.

Matt

Peter Schott SSCrazy Eights Points: 9674 More actions · Answer 5

Just a caution on that method - I was using it successfully for a while and then it just stopped working. No errors, no warning, just no execution. Worked great while I was using it.

-Pete

KeithDavies SSC Veteran Points: 267 More actions · Answer 6

Thanks to all who contributed.

I've got it running now by executing a job via stored procedure rather than using DTS package!

Regards

Keith Davies
IT Consultant