Bkuniyil
asked on
How to handle the error when a step in the SQL job fails
I created a job with about 4 steps that process data and uses ftp to send data to our customer.
If the job fails during any step , I want to log that into a table in the database with the error which caused the step to fail.
If the job fails during any step , I want to log that into a table in the database with the error which caused the step to fail.
ASKER
Actually the job step does not execute a stored procedure. It executes an SSIS package. When the package returns with an exit code other than 0 it is a failure. Hence the job fails . I need to trap this error and log into the database.
Experts, could you please help me in achieving this.
Assuming it's a SQL job you can do that directly in the job step under the "Advanced" like in the attached picture - either log to a table and/or include step output in the history and you can also send emil notifications on failure. If you need to log from the batch (cmd file) that does the FTP then you may need additional code and run SQL command shell to populate the table.
Ok, in that case the SSIS step that is executed need to include a BEGIN/TRY/CATCH set of SQL commands as "dbasql" suggested. Also you can configure the SSIS to do the Logging including in the Event long on that server.
Integration Services Log Providers
http://msdn.microsoft.com/en-us/library/ms140246(v=sql.90).aspx
Integration Services Log Providers
http://msdn.microsoft.com/en-us/library/ms140246(v=sql.90).aspx
ASKER
The requirement is to log the error in the table created in the database. I guess the "Log to table" option under the Notifactions does not help me here. Also the SSIS package is used to execute a batch file , so i would not be able to include a TRY/CATCH there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The solution partially resolved my problem.
http://www.sqlservernation.com/home/using-trycatch-in-sql-server.html
even better, the uspLogError procedure writes error details into the ErrorLog table. you can use this within each of your jobs steps as the action upon failure. then you can send an email with the error_line or error_number, with the 'JobName Failure' in the subject ine.
http://msdn.microsoft.com/en-us/library/ms179296.aspx