Pass back an error messafrom a DTS when ran through a stored procedure

[GJP55]

Is there any way to pass back an error message from a DTS when it is executed through a stored procedure ?

I have a DTS that is executed through a stored procedure.
If the DTS runs or fails, I get no indication of this unless I check that the DTS has done what it is suppose to have done.

The DTS imports , truncates and appends various tables and this is called using a stored procedure.

This is all ran from an Access front end and the stored procedure is called via a pass through query.

The code is :

....Declare various variables...
.....
-------------------Create Object----------------------------
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
-------------------Set the Package--------------------------
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSqlServer', NULL, 'MyServer', @PackageName='MyDTSImport', @Flags=256
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
----------------Execute the Package-------------------------EXEC @hr = sp_OASetProperty @oPKG
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
---------------Destroy the Package--------------------------EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
GO

 

[yksvaan]

Can't really provide you with an answer, since I haven't done this kind of dts, but to give you something to work with.. SQL manual says:
sp_OAMethod objecttoken, methodname , returnvalue OUTPUT ..
The last one being the return value of the method of the OLE object
@hr = sp_OAMethod
Being HRESULT returned by the OLE Automation object

So there's three return status involved (reverse order compared above):
package success (and inside that step success or failure)
ole automation objects status
object method's returnvalue

I think the first thing is to make sure the package fail in case it didn't do what it was supposed to. (You can force this for example with a sql statement RAISERROR('buggers',16,1)
It will stop the package, so it should be the last step.

My guess is you have to check the method's returnvalue and program user notification in the sp and return it to Access, or get the status to Access and program notification there.

I might be talking over my head, but that's how I would start trying to crack it.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]