Permission problems Executing DTS from Stored Procedure

[jupiter8]

I have found an example of how to execute a DTS Package from a Stored Procedure. And I have managed to get this to work without any problems on the development server.

However, the package does not execute on the live server almost certainly due to permission problems, what I have no idea what to do to solve this.

I am using a SQL Account which has permissions to execute the relevant OLE stored procedures in the Master Database so its not that. It seems more likely that its the security context that the DTS package is running in.

But all that said, I don't know what to change to get it to work.

 

[cehepker]

Hello,

What kind of error message are you getting? Do you have the same DSN's on both machines?

I am not sure what you procedure looks like that you are using to run the DTS. While I know there are several ways to do it, here is a copy of one way that we have found that works:
CREATE PROCEDURE [procRunDTS]
(
@PackageName varchar(100),
@Result int OUTPUT
)
AS

DECLARE @object int
DECLARE @hr int
DECLARE @errSource varchar(50)
DECLARE @errDesc varchar(250)

-- create DTS.Package object
EXEC @hr = sp_OACreate 'DTS.Package.1.0', @object OUTPUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @errSource OUTPUT, @errDesc OUTPUT
SET @Result = 1
SELECT @Result, 'Object Create Error: ', @errSource, @errDesc
SELECT @@SERVERNAME, @PackageName
GOTO ExitHere
END

-- load DTS Package from local SQL Server
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer', NULL, @ServerName=@@SERVERNAME, @ServerUserName='UserName', @ServerPassword='userpassword', @PackageName=@PackageName
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @errSource OUTPUT, @errDesc OUTPUT
SET @Result = 2
SELECT @Result, 'LoadFromSQLServer Error: ', @errSource,@errDesc
SELECT @@SERVERNAME, @PackageName
GOTO ExitHere
END

-- execute DTS Package
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @errSource OUTPUT, @errDesc OUTPUT
SET @Result = 3
SELECT @Result, 'Execute Error: ', @errSource,@errDesc
SELECT @@SERVERNAME, @PackageName
GOTO ExitHere
END

-- destroy created DTS Package
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @errSource OUTPUT, @errDesc OUTPUT
SET @Result = 4
SELECT @Result, 'Object Destroy Error: ', @errSource,@errDesc
SELECT @@SERVERNAME, @PackageName
GOTO ExitHere
END

SET @Result = 0

ExitHere:
RETURN




GO


Hope this helps,
Carla

Documentation: A Shaft of light into a Coded world