Execute package from SQL Stored procdure

[sjuarez1979]

Is it possible to execute a Data Transformation Package from a sql store procedure? Say the local package name is ImportData, what would the sql syntax be to execute that package. I have seen the use of running a shell that runs dtsrun.exe but I want to know if there is a better way.

Seth

 

[Andel]

You can use sp_cmdshell to execute dtsrun.
Or if you don't want to use that, you can schedule it to SQL Server Agent. Disable the schedule to make it run only when you kick off, then execute the job with a stored procedure.
Andel
andel@barroga.net

http://andyb.8m.com

 

[tlbroadbent]

Check the FAQ area of this forum for DTS resaources. One of those resources is the SQLDTS website. You can find different methods for executing a package at the following link.

http://www.sqldts.com/catlist.asp?nav=1,6,104,5

You could also create a JOB that executes the package and start teh job from a stored proc. See sp_start_job in SQL BOL. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Infy2000]

can i define my own procs/functions in local package?
if yes, then tell me how this can be done?

 

[tlbroadbent]

Infy2000,

I have two recommendations.

1) Read about DTS in SQL Books Online. There is a lot of information including a section titled "Adding Functionality to a DTS Package."

DTS Overview link:

http://msdn.microsoft.com/library/en-us/dtssql/dts_basic_5zg3.asp

2) Post new questions in a new thread. Many members may not look at this thread because there are several posts and they may assume the question has been answered. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

If a person was doing a lot of DTS work, Wrox has a book called 'Professional SQL Server 2000 DTS' that could be helpful.

 

[tlbroadbent]

I agree with bperry. Professional SQL Server 2000 DTS is an excellent resource.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.