DTS Error Reporting

[cLFlaVA]

Hi all,

Here's my scenario:

I have a DTS package with 8 separate steps. This DTS package drops and recreates a table, then fills the table with a bunch of data, and eventually updates a table in a linked oracle server with the collected data.

We are running this DTS package with a scheduled job, which runs nightly. The job has three steps: 1) executes the DTS package, 2) writes a record in an "exitstatus" table if step 1 executes successfully indicated it completed successfully, and 3) writes a record in the "exitstatus" table if step 1 fails, indicating a failure occurred.

Then we have a process that checks the exitstatus table to determine whether or not any failures occurred overnight. I'm sure this is not the best method, but it's been around here for a long time and it's what we need to work with for now.

My question is: is there a way to capture the specific error that occurred to force the DTS package (and therefore step 1 in the job) to fail? All we find out is that it failed - we never are able to determine at which step the DTS package failed, or why it failed. We would want to store the error in the exitstatus table.

Any help would be greatly appreciated.

Thanks!

Cory



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLDenis]

Logging to a text file:....

http://www.sqljunkies.com/Article/07DB1FCB-2F26-4A99-BD5F-15FA6F9F9B4A.scuk

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cLFlaVA]

I understand there are ways to log events to a text file. I am wondering if there is a way, programatically, to capture the error that made step 1 of a job fail, and write the error to the exitstatus table in step 2 or 3.

any ideas?



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLDenis]

type "DTS packages, error handler" in BOL, select Handling DTS Errors in Visual Basic
Expand Error Handler Example, there is some VB code that you should be able to use and modify

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/