Sql Server Agent ( How to execute DTS after executing store procedure

[123ASP]

Hi, I have created 3 store procedure and one DTS. I would like to schedule those code and run them in sequential order. Those are the code:
sp_A
sp_B
DTS_moveData
sp_C

Once sp_A is completed sucessfully, I want sp_B to run
then once sp_B is completed sucessfully, I want DTS_moveData to run[ This is where I am getting the most difficult part, where the code is not in store procedure it is in a DTS package, so how to run the DTS after my second sp_B and once the DTS is completed, I want to run sp_C ]
Thanks for your help,
Al

 

[JohnDTampaBay]

You could execute the DTS package from within a stored procedure using the sp_OA system stored procedures:

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

Or you could use the DTSRUN command line utility to run the DTS package. Either way, you can schedule one job to run all four steps in sequence. Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[123ASP]

Thanks,
Al

 

[MDXer]

Wouldn't it be easier to make sp1, sp2 part of your work flow in the dts package? You could use the ExecuteSQL_Task object string your Sp3 with an on sucess workflow, then when sp_b finishes have a workflow that is on success to the first step in what is now your dts package, after the last step in your current package set an on success workflow to sp_c.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck