Re: Execute SQL Task Error Capturing

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 07/06/04 

Date: Tue, 6 Jul 2004 18:02:43 +0100

In message <3D7C9B1A-B1EA-40FD-B1CD-6F105AF5C089@microsoft.com>, whornak
<whornak@discussions.microsoft.com> writes
>I did figure out that when running the tasks manually the progress
>window that stays open when done does contain the messages associated
>with the tasks. Is there a way to access that object?
>
>"whornak" wrote:
>
>> I want to build error capturing and reporting into the DTS packages.
>>I am currently using 'Execute SQL Task' and sending emails when the
>>SQL Statement fails. What I want to do is provide the recipient with
>>as much detail as possible about the error.
>>
>> How would I capture the actual error messages when a statement fails?

Four ways to get error details -

Use GetExecutionErrorInfo method for the step, but this is only an
option in strong typed code, such as VB, not VBScript.

Execute package WithEvents and capture the OnError events.

Log errors to a file, package level option. Note error file is not
available during the current package execution.

Log errors to SQL Server, package level option. You can access the
current execution by using the lineage Id global variables, see package
level option. Still probably done easier post execution or in a parent
package. 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Relevant Pages

  • Error attempting to execute a DTS package from an ADP
    ... "The Microsoft Jet database engine could not find the object '<my UNC ... I can run the package fine from there. ... -I tried restarting the SQL Server agent using the same user ID as the ... The execution of the following DTS Package failed: ...
    (microsoft.public.sqlserver.dts)
  • No Steps have been defined for the transformation Package
    ... The package runs fine on sql server. ... The execution of the following DTS Package succeeded: ... Dim cnn as SqlConnection ...
    (microsoft.public.sqlserver.dts)
  • Re: running a SSIS package from a scheduled job sql server 2005
    ... Profiler would allow us to trace the execution statements against SQL Server so we could have seen where we were which would have helped us find where we may be stuck. ... If I create a package that truncates a table and I include the package ...
    (microsoft.public.sqlserver.dts)
  • Re: bas file does nothing
    ... If a DTS package fails to execute, such as bad file, then this does not ... need to check that the package step execution result or use events to ... >occurs on the SQL Server side and VBA doesn't know about it? ...
    (microsoft.public.sqlserver.dts)
  • Re: Can you use osql in DTS?
    ... This sounds simple enough to do in a single package. ... ActiveX Script task to build the date and time strings to populate the ... Data Pump task that executes a proc that populates a table and then ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.dts)