Execute DTS Package from Access VBA

[RobLehman]

I want to execute a DTS Package stored on SQL Server from within VBA. Any ideas?

Thanks!

 

[Norris68]

I wrote a stored procedure that would execute a DTS package, however it required sa-equivalent rights to run it (dtsrun/xp_cmdshell - I can't remember which) - see SQL BOL for details. Our server at the time was called CBSFS02, so you should be able to modify the following:

CREATE PROCEDURE ExecDTSPackage
@PackageName varchar(255),
@ExecResult int OUTPUT
AS

DECLARE @result int
DECLARE @cmdline varchar(255)
SET @cmdline = 'dtsrun /S CBSFS02 /E /N "' + @PackageName +'"'
SET @result=0
EXEC @result = master.dbo.xp_cmdshell @cmdline, no_output
/* Return a boolean i.e. 0 = False, everything else = True */
IF (@result = 0) /* It worked */
SET @ExecResult = 1 /*True*/
ELSE
SET @ExecResult = 0 /*False*/

GO