DTSLookup error handling

Hello all,
Does any one know how I can capture the sql error message when the sql statement in DTSLookup step fails?
e.g.
I have this sql statement in a DTSLookup task:
insert into table1 (col1) values (?); select @.@.ERROR

Let's say for some reason (bad data), my insert statement fails and I want to know why. So I added "select @.@.ERROR" to capture the error. However, from the Lookup window, I cannot save the result of @.@.ERROR to a global variable or to an output parameter. I want to log this error in the next step that handles the OnFailure workflow, but I don't know how.

Can someone help please?

Thanks,
K.Hello all,

Can someone help please?

Thanks,
K.

Check out this link (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q221193).

From what I recall, you cannot handle DTS errors in VB Script, which is maddening to me. We sorta got around this by creating a VB DLL and then instantiated the VB DLL from within a calling DTS package. I've never been really happy with the solution, but I'm just the production DBA and not the "real" DBA :rolleyes: .

Regards,

hmscott