Scheduling legacy DTS from within SQL 2005

A useful little hack if you’ve installed support for DTS in SQL Server 2005 but also need a way of scheduling the packages. The solution I came up with (warning: not pretty) was to enable the xp_cmdshell system stored procedure and make an external call to the DTSrun program. Of course, you would be better off just calling it as a scheduled task from windows, but in case that’s not an option (for whatever administrative reasons), here’s the workaround…

EXEC sp_configure 'show advanced options', 1
GO
---- To update the currently configured value for advanced options.
RECONFIGURE
GO
---- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
---- To update the currently configured value for this feature.
RECONFIGURE
GO

--Now call the dts run feature using this code segement....
declare      @Cmd varchar (200)
select      @Cmd = 'dtsrun /Sserver /E  /Npackage'
exec      master..xp_cmdshell @Cmd

Not pretty and not very secure either. Still, very useful in a tight spot.