execute dts from a stored procedure

Question

execute dts from a stored procedure

talkme

SSC Enthusiast

Points: 156
More actions
September 10, 2003 at 7:13 am

#162355

Hi!!! Can you help me?
How cai i execute a dts fro a stored procedure? Is there a command to do it?
tks

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

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

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

You can use xp_cmdshell to run dtsrun, but perhaps it would be better to add a record to a control table, and have a scheduled task watching that table and running the dts job for you.

Steven

philcart SSC-Forever Points: 47796 More actions · Answer 2

You can also use the sp_OA procedures, but they tend to be a bit troublesome.

The best approach I've come up with is to setup a scheduled job to run the DTS package. Make sure the job runs successfully. Then disable, or delete, the schedule. Then when you want to run the package just call sp_start_job to run the scheduled job.

From an application point of view, you can put a wrapper around sp_start_job that checks if the job exists or is already running.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 3

Yes, I agree with Phill there ....if the DTS is a scheduled job then its always better to maintain it...but again it depends on what you are trying to do with the DTS...

Cheers!

Arvind

Russ Bell SSC Enthusiast Points: 192 More actions · Answer 4

I've found really great DTS help at the following URL: http://www.sqldts.com/

There is a specific article on using sp_OA* procedures to call DTS:

http://www.databasejournal.com/features/mssql/article.php/1459181

abeljda Say Hey Kid Points: 675 More actions · Answer 5

Another option is to set up a SQL Agent job (schedule it to run or not at all) then call the stored proc sp_Start_Job. I am using that to allow the user to click a button to kick off a dts package.

Brookstone SSCommitted Points: 1745 More actions · Answer 6

I'm running into a problem with permissions. When I try to exec sp_start_job from a stored procedure using a datareader login the DTS within the scheduled fails. It only works when I'm logined as an administrator. How do I get around this?