Calling a DTS from a stored procedure

Question

Post reply

Calling a DTS from a stored procedure

paulsomr

SSC Rookie

Points: 26
More actions
September 16, 2003 at 8:10 am

#65411

How do I call a saved dts package from a stored procedure?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

xp_cmdshell 'DTSRUN.EXE ....', See BOL DTSRUN for details.

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

You can use the method Allen_Cui suggested, or you can use sp_OA* procedures.

Check out www.sqldts.com for detailed info.

The best method I've found is to schedule the Package and diasable, or remove, the schedule in the job.

Then use sp_start_job to execute the package.

Hope this helps

Phill Carter

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

Colt 45 - the original point and click interface

Edited by - phillcart on 09/16/2003 5:56:20 PM

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

alphaindex SSC Veteran Points: 259 More actions · Answer 3

One of the problems with xp_cmdshell 'DTSRUN.EXE etc is you can't tell if the DTS package ran successfully or not, so I would look at Phil Carters suggestion. My other point would be to re-desing your project so the DTS package calls the stored proceedure rather than the other way round.

P

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

I have used exec sp_start_job @Job_Name ='dtspackagename', it works.

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

I should have mentioned this catch, you need to set up a sql server agent job that executes the dts package. I also created a sql task in the dtspackage to update a status table so I know for sure that the dts package completed successfully. So what you have, is a stored procedure, that calls sp_start_job to start a sql agent job, that executes a dts package. Seems like a lot to get to a package, but it gets the job done.

Dima-99208 SSC-Addicted Points: 499 More actions · Answer 6

I use:

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

execute master.dbo.xp_cmdshell 'dtsrun /SserverName /UloginName /Ppassword /NDTSpackageName /M'

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

replace: serverName = your SQL server,

loginName = your login,

password = password for your login,

DTSpackageName = your DTS package

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 7

One thing worth considering when deciding whether to run your package via a SQL Agent job, or via xp_cmdshell is timing of when the sp executes the statement after staring the SQL Agent job or xp_cmdshell statement. If you use the SQL Agent job approach then the SP will start the job and continue on to the next sp statement, without waiting for the DTS package to complete. Where as the xp_cmdshell will not process the next statement in the SP, until after the DTS package is done. So depending on your follow-on SP code, you may not desire the asynchronous logic that the SQL Agent job approach. But then again you may like the idea of asynchronous logic if the DTS package takes time, and this SP is executed via a web page or some other online process.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

DAVNovak Hall of Fame Points: 3120 More actions · Answer 8

AlphaIndex,

You state: "One of the problems with xp_cmdshell 'DTSRUN.EXE etc is you can't tell if the DTS package ran successfully or not, so I would look at Phil Carters suggestion. "

However, this is not true. You can execute the xp_CmdShell 'DTSRUN ....' command and capture a return value that tells you if it was successful or not. For example..

DECLARE @iResult int

EXEC @iResult = Master..xp_CmdShell 'DTSRUN ...'

IF @iResult = 1

ELSE

Dave

DAVNovak Hall of Fame Points: 3120 More actions · Answer 9

I forgot that you can have the DTS write to an error log if there are any issues. This can be done in SQL 2000, I do not remember if it can be done in SQL 7.0.