Trapping ExecSQLTask Errors

Question

Post reply

Trapping ExecSQLTask Errors

Bruce Pettus

Old Hand

Points: 374
More actions
August 31, 2004 at 12:57 pm

#162015

How does one trap the specific SQL Server error from a failed ExecSQLTask object without enabling package logging? I'd like to perform certain functions if certain errors are returned from the SQL Server. I could write a ActiveX script that creates a connection object and executes the SQL statement, but that would defeat the purpose of having an ExecSQLTask object.
Tanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

Follow this link which will give a detailed idea about the error trapping.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;240221

Thanks,

Ganesh

Bruce Pettus Old Hand Points: 374 More actions · Answer 2

I don't want to enable package logging because I'm already maintaining an execution log that gives me much more information then package logging. Additionally, If I get an error from an ExecSQL step that I can compensate for within my code, then package logging doesn't help.

Thanks for your thoughts,

Bruce

philcart SSC-Forever Points: 47795 More actions · Answer 3

But the error from the ExecSQL task will be in the package logging tables for you to query? Once you've extracted it you can act on it.

Also, do you know about the WriteTaskRecord and WriteStringToLog methods? Both of these allow you to write your own messages to the DTS Package Log.

EG: DTSPackageLog.WriteStringToLog 'My message'

DTSPackageLog.WriteTaskRecord -1, 'My error message'

--------------------
Colt 45 - the original point and click interface

Bruce Pettus Old Hand Points: 374 More actions · Answer 4

I was not aware of the DTSPackageLog methods. I think they will solve my problem nicely.

Thanks.

P.S. - I like your graphic with the guy bangin' his head on his desk. Thanks for the giggle.

Bruce Pettus Old Hand Points: 374 More actions · Answer 5

I have researched the DTSPackageLog in greater detail. I found a statement that says the package log object is only available in transformations. Is this true? I'd like to use in plain ActiveX script tasks that are not involved in any sort of transformation.

Am I missing Something?

philcart SSC-Forever Points: 47795 More actions · Answer 6

You can use it in the ActiveXScript task. The syntax is just like I've illustrated in my post above. With the WriteTaskRecord method, if the number passed as the first parameter is positive, when youview the log via the GUI the entries show up with a green tick instead of a red cross.

One thing to be aware of is that this will generate an error if you're manually running the task in the designer. eg: right-click task and choose "Execute step"

This is because when you execute the task this way the DTS package log is not open. I haven't had a chance to see if there is a way to workaround, or avoid, this.

--------------------
Colt 45 - the original point and click interface

simon.slade Newbie Points: 6 More actions · Answer 7

If I execute a child package,on return to the parent the DTSPaackageLog calls dont apperar to work. They dont fail, but nothing gets logged. Is this a known issue ?

philcart SSC-Forever Points: 47795 More actions · Answer 8

One thing I can think of at the moment is to check that you have Package Logging enabled in the Child Package as well as the Parent Package.

Don't have a server that I can simulate this anymore. All the transformation stuff I do these days is in SSIS 😀

--------------------
Colt 45 - the original point and click interface