I need to create a sp such that it calls a DTS package with in a SP

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

To run a DTS packae directly, you would have to use xp_cmdshell, which is not recommeded.

I would instead create a SQL Agent job that runs the package and then start it with msdb..sp_start_job.

-- Gianluca Sartori

deepankar.bhardwaj SSC Enthusiast Points: 161 More actions · Answer 2

Thanks Mate..:-) can you throw more light how exactly we can use sp_start_job in this case.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Create a job with appropriate name (e.g. 'DO SOME STUFF').

Add new operating system step and set the command as follows:

DTSRUN /E /S<serverName> /N<DTSPackageName>

Save the job.

Open a query window and issue:

EXEC msdb.dbo.sp_start_job @job_name = 'DO SOME STUFF'

-- Gianluca Sartori