SSIS Event Handler (OnError) Fires even with no error.

Question

SSIS Event Handler (OnError) Fires even with no error.

houman.fathi

SSC Enthusiast

Points: 185
More actions
September 11, 2023 at 6:02 pm

#4284351

I have a SSIS package and set Event Handler (OnError) to send me a message with logs:
"Package: " + @[System::PackageName] + "\n" +"Start Timet: " + (DT_WSTR, 40) @[System::StartTime] + "\n" +"User Name: " + @[System::UserName] + "\n" +"Machine Name: "+ @[System::MachineName] + "\n" +"Task Name: " + @[System::SourceName] + "\n" +@[System::ErrorDescription]
The problem is, it fires and sends me a message for each component, even when they run successfully. I even disabled all components temporarily, but still received emails from it. I can put all components in one big ForEach component, but as it needs much memory, I don't prefer to do that.
Furthermore, I checked all warning and fixed them as well. Rebuild the solution. Disabled / enabled all components.

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

According to the MS docs, that shouldn't be happening. As a really random thought - is it possible that an error did occur, just not the way you were thinking? Does the "Error Description" have a value? What I am wondering is if MAYBE something is erroring and needs to be corrected (such as a bad connection string)...

If you are doing this in Visual Studio and the mail is going out, does the same thing happen when deployed to an SSIS instance? I am wondering if MAYBE it is a bug in visual studio or your specific version of SSDT.

Also, if your error handling is JUST sending an email (ie no cleanup or anything), I tend to build those into the SQL Agent job instead. If the job fails, then send the DBA's an email. I don't like my SSIS packages eating up memory longer than they have to.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

houman.fathi SSC Enthusiast Points: 185 More actions · Answer 2

Thank you, but the weird thing is it sends me the right message with details when there is a real error. It means it works accurately when there is an error.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

I'm still thinking this is either:

A - expected behavior <-- that is there is an error message being sent in the email notification

B - bug in Visual Studio or SSDT <-- this could be just in the specific version you are using

To validate point B, you could deploy your package to a test SSIS server and see if you get the same incorrect email notifications. To validate point A, you could review the value of the error message and see if it contains any useful info about why it errored.

If you have an "onerror" event handler set up, it SHOULD only trigger when there is an error. Any chance you have another event handler in place that is configured for a different thing such as "OnProgress" perhaps?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

houman.fathi SSC Enthusiast Points: 185 More actions · Answer 4

Thanks for respond.

I have only one ErrorHandler, and it returns emails with empty body when components work properly.

So, I cannot track if there are any errors anywhere.

For your first solution, I will try to deploy it in test environment to see the result.

Thanks,

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

Did it work better when run outside of Visual Studio?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.