Link to home
Create AccountLog in
Avatar of Dipali051900
Dipali051900Flag for United States of America

asked on

Executing DTS from a Stored Procedure

I have an asp the executes a sproc that in turn executes a dts package. When I execute the sproc from the QA
EXEC SYSExecuteDtsPackage 'Employee_txt'
GO
I get:
(1 row(s) affected)
LoadFromSQLServer failed
OLE Automation Error Information
 sp_OAGetErrorInfo failed.

Where do I start troubleshooting this ?

-- Stored procedure that executes the dts
ALTER PROCEDURE [dbo].[SYSExecuteDtsPackage]
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
AS
DECLARE @hr INT
DECLARE @object INT
If Exists (SELECT * from sysobjects where id=object_id(N'[dbo].[LocationHistUpdateSYS]')
and OBJECTPROPERTY(id,N'IsUserTable')=1)
DROP TABLE [dbo].[LocationHistUpdateSYS]
CREATE TABLE [dbo].[LocationHistUpdateSYS] 
(SupervisorPersonID varchar(15),
EmployeeID varchar (15))
--CREATE a package object 
EXECUTE @hr = sp_OACreate 'DTS.Package', @object OUTPUT 
select @hr
IF @hr <> 0
BEGIN
EXEC SYSDisplayOLEAutomationErrorInfo @object --, @hr
RETURN @hr
END 
--load the package (ADD integrated security support)
DECLARE @svr varchar(15)
DECLARE @login varchar(15)
SELECT @login = SUSER_NAME()
SELECT @svr = HOST_NAME()
DECLARE @flag INT
SELECT @flag = 0
IF @intsecurity = 0 
IF @userpwd = Null
EXECUTE @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
ELSE
EXECUTE @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
ELSE
BEGIN
SELECT @flag = 256
EXECUTE @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
END
IF @hr <> 0
BEGIN
PRINT 'LoadFromSQLServer failed'
EXECUTE SYSDisplayOLEAutomationErrorInfo @object --, @hr
RETURN @hr
END
--execute it
EXECUTE @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
PRINT 'Execute failed'
EXECUTE SYSDisplayOLEAutomationErrorInfo @object --, @hr
RETURN @hr
END
--return the step errors as a recordset
EXECUTE SYSDisplayDTSPackageErrors @object
-- unitialize the package
EXECUTE @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT 'UnInitialize failed'
EXECUTE SYSDisplayOLEAutomationErrorInfo @object --, @hr
RETURN @hr
END
-- release the package object
EXECUTE @hr = sp_OADestroy @object 
IF @hr <> 0
BEGIN
EXECUTE SYSDisplayOLEAutomationErrorInfo @object --, @hr
RETURN @hr
END

Open in new window

Avatar of Dipali051900
Dipali051900
Flag of United States of America image

ASKER

anyone ???
Avatar of Anthony Perkins
Would you consider an alternate approach?
Hi,

Have you enabled OLE Automation in the surface Area config Manager?

Cheers
  David
acperkins - The client has 10 interfaces in place - they would not agree to another approach because of the cost and time involved - what other options did you have in mind though ?

dtodd - Yes it is enabled.
>>they would not agree to another approach because of the cost and time involved <<
Fair enough.

>>what other options did you have in mind though ?<<
Not using OLE Automation.
Thanks acperkins ~ I am desperate - maybe your suggestions will be less time-consuming then trying to fig out the error. Do you have something that I can use? The sproc would get executed from an asp. The dts package would get executed from the sproc. I do not have time to convert the dts packages to SSIS packages - there are a lot.

Appreciate your time/advise.

D
I would just use the xp_cmdshell to execute DTSRun.  See here:
Execute a package from T-SQL
http://www.sqldts.com/210.aspx
ASKER CERTIFIED SOLUTION
Avatar of Dipali051900
Dipali051900
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer