Procedure to execute a dts package

Question

Procedure to execute a dts package

Thuan Pham

Old Hand

Points: 365
More actions
June 13, 2005 at 9:58 am

#68017

Hi
I wonder if anybody can help me with a procedure to execute a dts package. I know we can schedule this to run daily but I want the procedure because I want to ran a command delete some records everytime before I run the DTS package to import records. Maybe there is a different way to do this. Right now I have to manually delete records I need to delete and then run the DTS. If I can combine the two jobs into one, it would be great. Thank you

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

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

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 1

You could make the first step of the DTS package delete the records in question (and connect it to the existing steps in the package with an On Success), then you would have the whole process in one neat package.

Otherwise, look into using xp_cmdshell and the DTSRun utility ('dtsrun utility' and 'dtsrunui' in SQL Server books online).

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 2

If you go with Chris' second option, as an example, the syntax I generally use is:

EXEC master..xp_cmdshell 'dtsrun /S server_name /N package_name /E'

The /E means trusted connection (no username and password required) but depending on your setup you may have to provide one. If your package has a password you'll need to supply that using the /M switch.

As Chris said though everything is available in BOL and it's fairly straightforward.

Nilssond Hall of Fame Points: 3285 More actions · Answer 3

CREATE procedure sp_runpkg @t varchar(100)

as

declare @s-2 nvarchar(200)

set @s-2 = 'master.dbo.xp_cmdshell ''dtsrun /S<YourServerName> /E /N' + @t + ''''

exec master..sp_executesql @s-2

GO

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 4

Why use dynamic SQL; any specific reason? Why not just this:

CREATE procedure sp_runpkg @t varchar(100)

as

declare @s-2 nvarchar(200)

set @s-2 = 'dtsrun /S /E /N' + @t

exec master..xp_cmdshell @s-2

GO