frere
I have tried both examples in a recent project
1. xp_cmdshell
2. OLE Automation Stored Procedures
Using xp_cmdshell didn't give me the flexibility of responding to failures,
it will write it to the event log using the /W True option but my proc
needed a response if the DTS Package fialed.Each OLE Automation stored
procedure returns an integer code that is the HRESULT returned by the
underlying OLE Automation operation. If HRESULT is equal to 0, then
everything is okay, a nonzero HRESULT indicates OLE error.
Here is an example of OLE Automation using the LoadFromSQLServer Method
Here is the Method signature:
Public Overridable Sub LoadFromSQLServer(ByVal ServerName As String,
Optional ByVal ServerUserName As String = Nothing,
Optional ByVal ServerPassword As String = Nothing,
Optional ByVal Flags As DTS.DTSSQLServerStorageFlags = 0,
Optional ByVal PackagePassword As String = Nothing,
Optional ByVal PackageGuid As String = Nothing,
Optional ByVal PackageVersionGuid As String = Nothing,
Optional ByVal PackageName As String = Nothing,
Optional ByRef pVarPersistStgOfHost As Object = "")
--Permissions
--Only members of the sysadmin fixed server role can execute sp_OACreate, sp_OAMethod
CREATE PROCEDURE dbo.sp_ExecuteDts
@ServerName varchar(30),
@PackageName varchar(100)
AS
--These variables can be hard coded if you don't plan on reusing the proc
--@ServerName = Name of the Server
--@PackageName = Name of the Dts Package
DECLARE @object int
DECLARE @hr int
DECLARE @Flags int
SET @Flags=256 -- 0 Use SQL Server Authentication or 256 Use Windows Authentication
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
RAISERROR ('Error creating DTS.Package',16,1)
RETURN
END
--should be on one line
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer', NULL,@ServerName,'','',@Flags,'','','','@PackageName',''
IF @hr <> 0
BEGIN
exec sp_displayoaerrorinfo @object, @hr
RAISERROR ('Error LoadFromSQLServer',16,1)
RETURN
END
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
RAISERROR ('Execution failed',16,1)
RETURN
END
GO
--///////////////
Here is an example using xp_cmdshell, this proc must be stored in the master database
CREATE PROCEDURE dbo.ExecuteDts
AS
EXECUTE xp_cmdshell "DTSRun /S Server3 /E /N dtsAdd"