sp_oaexecute to error log

Question

sp_oaexecute to error log

dan_roche

SSC Rookie

Points: 33
More actions
March 11, 2010 at 10:45 am

#212371

I'm trying to execute a dts from a sproc but it's failing on execute. oPKG is valid and my global input variable has the value I want. It fails on execution in query analyzer with
source: Microsoft Data Transformation Services (DTS) Package
description: Package failed because Step 'DTSStep_DTSActiveScriptTask_1' failed.
I have the error handling for the package set to go to a file on my c drive, but the file never gets created if I run it through the stored procedure.
If I hardcode the global variable in the Package and run it from Enterprise Manager, it works fine. The global variable is a filename and path (\\server\share\folder\file).
Is there a way to have errors logged when using the sp_oa implementation? Can anyone point me in the right direction?
@inputfile nvarchar(1000)
AS
declare @hr int
declare @oPKG int
declare @cmd varchar(1000)
declare @server varchar(100)
declare @pkgpwd varchar(1)
declare @pkgname varchar(100)
set @server = 'MyServer'
set @PkgPwd = ''
set @PkgName = 'MyPackage'
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer Failed.'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
EXEC @hr = sp_OASetProperty @oPKG,'GlobalVariables.item("inputfile").value', @inputfile
IF @hr <> 0
BEGIN
PRINT '*** Set Property Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
if @hr<>0
begin
PRINT '*** Execution Failed'
--EXEC sp_displayoaerrorinfo @oPKG, @hr
declare @source varchar(1000)
declare @desc varchar(1000)
exec sp_oageterrorinfo @oPKG,@source out, @desc out
print @opkg
print 'source: '+@source
print 'description: '+@desc
--RETURN 1
end
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
begin
PRINT '*** Uninitialize Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
end
EXEC @hr = sp_OADestroy @oPKG

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 1

I stongly discourage this kind of constructs.

sp_oa stuff is known to frequently suffer memory leaks, ... which may cause your sql instance to be unstable and crash or reboot.

Workaround:

Create a sqlagent job for your dts package execution and set up an alert to start that job.

Have a look at: http://www.sqlservercentral.com/scripts/Miscellaneous/31032/

This way you'll have all controle over if the job /alert is active, you can shut it down during maintenance, ...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

dan_roche SSC Rookie Points: 33 More actions · Answer 2

ALZDBA, thanks for looking at this.

I'm new to DTS and Stored Procedures. And I was having a hard time finding a best practices with details on implementation.

I only want to fire the DTS when a user imports a file, which means I have to pass a variable.

Can you give me an example on what I should put in the Job Step T-sql? Also how do I fire the Job from Visual Basic?

Thanks

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 3

I would just feed the varriables value in a "variables" table and have the job or dts pick it up from there.