Steve,

You can execute a DTS package from a stored proc. I'll include a stored proc that does just that, but it's not the best way, IMO. If you just want to automate the process on some type of schedule, just use SQL Agent and create and schedule a job to run both your stored proc and your DTS package.

But, if you still need to do it, here it is:

-- ============================================================================
-- Author:		J. Chad Bourque
-- Create date: July 18, 2006
-- Description:	Execute a DTS package.
-- Paramaters:	@package - Name of the DTS package to execute.
--					Defaults to null (nothing is run).
-- ============================================================================
ALTER PROCEDURE [dbo].[dw_ExecuteDTS]
(
	@package varchar(50) = null
)
AS
begin
	set nocount on

	declare @cmd varchar(100)

	-- Build the command line to run.
	set @cmd = 'dtsrun /S (local) /N ' + @package + ' /E'

	-- Execute the command if it is not null.
	if (@cmd is not null)
		exec master..xp_cmdShell @cmd
end

You'll have to open up the security for the user calling this, though.

HTH,
Chad

>Can anyone help me with above? I have the stored procedure that gets required information, and the DTS package that exports the data, but now need to automate the process.
>
>Thanks
>Steve