SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Serving Warm SSIS Errors


Serving Warm SSIS Errors

Author
Message
Frank Banin
Frank Banin
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 481
Comments posted to this topic are about the item Serving Warm SSIS Errors

Frank Banin
BI and Advanced Analytics Professional.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123903 Visits: 13344
Nice article, but why don't you use the system variable @ErrorDescription, instead of fetching it from the logging table?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
johnbrown105 56149
johnbrown105 56149
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 1206
... sp_send_dbmail ... which is stored in the MSDN database ...


Surely you meant the msdb database.
paulhutagalung
paulhutagalung
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 308
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
kpatrick
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 89
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
ggareth
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 424
Nice article but my company's security policy requires Database Mail to be deactivated. We use the SSIS 'Send Mail' task instead.
MWise
MWise
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1960 Visits: 1820
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
cliffb
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2039 Visits: 438
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 Banin
Frank Banin
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 481
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
sqlserver8650
Right there with Babe
Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)

Group: General Forum Members
Points: 791 Visits: 774
Thanks

Good Article
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search