Serving Warm SSIS Errors

Question

Serving Warm SSIS Errors

Frank A. Banin

SSCommitted

Points: 1620
More actions
April 11, 2012 at 11:45 pm

#254661

Comments posted to this topic are about the item Serving Warm SSIS Errors
Frank Banin
BI and Advanced Analytics Professional.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Nice article, but why don't you use the system variable @ErrorDescription, instead of fetching it from the logging table?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

johnbrown105 56149 SSC-Addicted Points: 493 More actions · Answer 2

... sp_send_dbmail ... which is stored in the MSDN database ...

Surely you meant the msdb database.

paulhutagalung Valued Member Points: 70 More actions · Answer 3

I prefer to use logging table as I need to collect events I need from multiple components or even packages and report it at scheduled time.

the problem for this script is that instead of using ssis components for email, we have to use senddbmail which mean setting a mail profile at database is a must.

is there any way to use ssis mail task with body contains html formatted strings?

kpatrick SSC Enthusiast Points: 188 More actions · Answer 4

SSIS already has a built-in component for sending email. What are the advantages of using a store proc that calls sp_send_dbmail over sending the email directly from the SSIS package? You can access the error code and error description as package variables and it seems you'd get more flexibility using SSIS?

ggareth SSCommitted Points: 1589 More actions · Answer 5

Nice article but my company's security policy requires Database Mail to be deactivated. We use the SSIS 'Send Mail' task instead.

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 6

We can't use the SSIS Send Mail task because the SMTP connector does not support a remote SMTP server that requires a user id and password. We use sp_send_dbmail and a specific SQLAlert profile for all of our error notifications.

MWise

cliffb SSCarpal Tunnel Points: 4597 More actions · Answer 7

It's a good article and approach. I am confused however as to how you are getting just one email to send. Internally where I work we use a custom email component (handles formatting and some other things beyond what is available in the default task) and for every time the OnError event fires, an email is sent. What your procedure would do, if I understand correctly, is still send multiple emails, it's just that the last email would have all errors accounted for. Wouldn't a better design be to call that procedure on Post excute of the package to ensure only one error email was sent?

If I am not getting something, please let me know. I'd love to have something we can implement here where we only get one email.

Frank A. Banin SSCommitted Points: 1620 More actions · Answer 8

CliffB,

You are right, the task that calls the SP should be assigned to the on post execute event, I made this change after the article was approved so I it did not take.

Sorry for joining the party a little late I am actually on vac with limited Internet access. But let me know if you have any questions.

Thanks

Frank Banin
BI and Advanced Analytics Professional.

sqlserver8650 Ten Centuries Points: 1121 More actions · Answer 9

sqlserver8650

Ten Centuries

Points: 1121

April 12, 2012 at 12:27 pm

#1472864

Thanks

Good Article

dgreen-1126628 SSC-Addicted Points: 447 More actions · Answer 10

[font="Courier New"]Here's the way I send out a single error message (error message 1) to my pager.

1) Set up a variable at the package level as an error counter; such as "ErrorCounter" as Int32

2) Add a SQL Task to the OnError event handler at the package level.

3) Connect it to your SQL Server database and use direct input with the code "select ? + 1 as counter" to increment

4) In Parameter Mapping on the task, use the "User::ErrorCounter" variable as Input as LONG for Param1 with size -1

5) In Result Set, use "counter" as Result Set and "User::ErrorCounter" as the variable

6) Add a Send Email task to the event handler and connect the SQL task to it with a Precedence Constraint

7) Set the Precedence Constraint to Evaluate an "Expression" and set the expression to "@ErrorCounter==1"

8) add the email info to send the email (in my case to my pager)

9) Add two Expressions to your Send Mail task:

(1)MessageSource with formula = "Error No.: " + (DT_STR, 10, 1252) @[User::ErrorCounter] + "\rError Code: " + (DT_STR, 25, 1252) @[System::ErrorCode] + "\rError Description: " + @[System::ErrorDescription]

(2)Subject with formula = "Error No.: " + (DT_STR, 5, 1252) @[User::ErrorCounter] + " in SSIS Package: " + TRIM( @[System::PackageName] )

I also send out all the error messages to my email. To do this, copy the Send Email Task and attach it to the SQL Task

with a normal Precedence Constraint (green line) and the emails will flow in one at a time per error message. They

are usually not that helpful, but sometimes they can be. I don't mind having all the emails to look at when I get paged.

I seldom get paged, but when I do, I don't mind looking at the emails to find the issue(s). I also have not yet had

to deal with logging errors in SSIS using my method.[/font]

Kevin Kunz-193240 Valued Member Points: 73 More actions · Answer 11

A nice article, but for people to "drive and learn" directly from the article they would need your table scripts for error logging - that or decipher the stored procedure, ensuring proper data types and lengths in order to get the process to run.

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 12

Kevin Kunz-193240 (4/12/2012)
A nice article, but for people to "drive and learn" directly from the article they would need your table scripts for error logging - that or decipher the stored procedure, ensuring proper data types and lengths in order to get the process to run.

The article uses the standard sysssislog table that is created when logging is enabled in a package. Although I do believe the stored procedure is limited to SQL 2008 and above. IIRC, SQL 2005 used a different table name. I was able to follow the steps in the article and send myself some nicely formatted error messages with all the necessary details.

MWise

Singanan Krishnasamy SSC Enthusiast Points: 192 More actions · Answer 13

good stuff. appreciate for the wonderful demonstration.. thanks a lot,

meanwhile, I am thinking that we need to have some alternate ways as well to capture error log information (* in addtition to database table) ...the reason is that, what if we have connection issues to the errorlog table itself ?

DBGuy1611 Grasshopper Points: 15 More actions · Answer 14

Useful article. I too prefer to use the send mail task but thought provoking nonetheless. Thanks for taking the time to pull the article and samples together!!!!