Using Windows Task Scheduler to detect SSIS package failure

Question

Using Windows Task Scheduler to detect SSIS package failure

Edding Jan

SSCommitted

Points: 1568
More actions
August 2, 2016 at 12:02 pm

#312267

I'm using Windows Task Scheduler with 32 bit dtexec.exe to execute an SSIS package and I was wondering how I would go about detecting whether the package succeeded or not? Even when the package fails, the event (as seen in the Event viewer) for the task indicates it succeeded. Technically, the task did succeed, it's just that it succeeded at executing a package that fails. I'd like to be able to detect the failure so I can set up another task to send out an appropriate email to me notifying me of the failure.
Note that I cannot use SQL Server Job Agent as it won't exist on the server that will be used to execute the jobs. I'm just trying to get this to work on my PC at the moment (which has Job agent) in preparation for this move to the new server.
Any help would be appreciated.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

Can you modify the packages? If so you should be able to add an event handler to write to either a log file or the Windows Event Log.

Integration Services (SSIS) Logging

https://msdn.microsoft.com/en-us/library/ms140246(v=sql.120).aspx

Joie Andrew
"Since 1982"

Edding Jan SSCommitted Points: 1568 More actions · Answer 2

Thanks for the response. Yes, I can modify the packages. Your suggestion sounds good. In connection to it, something else I was working on that seems to work is to reference a batch file in the task which executes the package. In the batch file I can check the ERRORLEVEL environment variable after dtexec is run to see if it's not equal to 0. If it isn't, I then use the eventcreate command to insert an appropriate error event in the Application event log. I can then use another task to be triggered when it finds such an event etc. But your method may work as well. I will look into it. Thanks!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

That might work, just remember that those error codes are for dtexec's ability to run a package, not the success/failure of a particular part inside the package itself. For that you will need SSIS logging.

dtexec Utility (SSIS Tool)

https://msdn.microsoft.com/en-us/library/ms162810.aspx

dtexec (SSIS Tool): Exit codes Returned
When a package runs, dtexec can return an exit code. The exit code is used to populate the ERRORLEVEL variable, the value of which can then be tested in conditional statements or branching logic within a batch file. The following table lists the values that the dtexec utility can set when exiting.
Value
Description
0
The package executed successfully.
1
The package failed.
3
The package was canceled by the user.
4
The utility was unable to locate the requested package. The package could not be found.
5
The utility was unable to load the requested package. The package could not be loaded.
6
The utility encountered an internal error of syntactic or semantic errors in the command line.

Joie Andrew
"Since 1982"

Edding Jan SSCommitted Points: 1568 More actions · Answer 4

Thanks for the info. I looked into adding an Event Handler to the package and I think that is the route I'm going to take (I'm relatively new to SSIS so I wasn't very familiar with SSIS event handlers initially). I figured I would just add an OnError event handler at the package level which will then execute a Send Mail Task to email me when the package fails. I assume that should work. Or maybe I will add an OnError Event handler to each of the tasks which could then help me figure out what went wrong in the package more easily. Thanks for your ideas!

Edding Jan SSCommitted Points: 1568 More actions · Answer 5

On a related note, I figured I would add an OnError Event Handler to each of the tasks in my packages. Each of these handlers will have a script task that will send out an email (using VB)indicating which task failed. I figured I would create some kind of email function/sub routine that will handle generating and sending the email out. I'm just not sure where exactly to define this function so that it can be accessed by all the tasks in the package. Should I make a separate class other than ScriptMain and put the email function in there? If you have any ideas, please let me know. Thank you!

suiqianqian7757 Grasshopper Points: 23 More actions · Answer 6

Edding Jan - Wednesday, August 3, 2016 1:31 PM
Thanks for the response. Yes, I can modify the packages. Your suggestion sounds good. In connection to it, something else I was working on that seems to work is to reference a batch file in the task which executes the package. In the batch file I can check the ERRORLEVEL environment variable after dtexec is run to see if it's not equal to 0. If it isn't, I then use the eventcreate command to insert an appropriate error event in the Application event log. I can then use another task to be triggered when it finds such an event etc. But your method may work as well. I will look into it. Thanks!

Hey.
Thanks for sharing. I'm wondering if your method was successful or not. I met exactly the same situation with you. If you don't mind, could you share more about how you check the ERRORLEVEL environment variable, and how to insert an error event and trigger another task by this event?

Thanks in advance!