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"